[Home] [Help]
PACKAGE BODY: APPS.ENG_ATTACHMENT_IMPLEMENTATION
Source
1 package body ENG_ATTACHMENT_IMPLEMENTATION as
2 /*$Header: ENGUATTB.pls 120.22 2006/10/11 07:21:52 pdutta noship $ */
3 -- Global constant holding the package name
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) :=
6 'ENG_ATTACHMENT_IMPLEMENTATION' ;
7 -- For Debug
8 g_debug_file UTL_FILE.FILE_TYPE ;
9 g_debug_flag BOOLEAN := FALSE ; -- For TEST : FALSE ;
10 g_output_dir VARCHAR2(80) := NULL ;
11 g_debug_filename VARCHAR2(30) := 'eng.chgmt.attachment.log' ;
12 g_debug_errmesg VARCHAR2(240);
13 -- Seeded approval_status_type for change header
14 G_ENG_NOT_SUBMITTED CONSTANT NUMBER := 1; -- Not submitted for approval
15 G_ENG_READY_TO_APPR CONSTANT NUMBER := 2; -- Ready for approval
16 G_ENG_APPR_REQUESTED CONSTANT NUMBER := 3; -- Approval requested
17 G_ENG_APPR_REJECTED CONSTANT NUMBER := 4; -- Approval rejected
18 G_ENG_APPR_APPROVED CONSTANT NUMBER := 5; -- Approval approved
19 G_ENG_APPR_NO_NEED CONSTANT NUMBER := 6; -- Approval not needed
20 G_ENG_APPR_PROC_ERR CONSTANT NUMBER := 7; -- Approval process error
21 G_ENG_APPR_TIME_OUT CONSTANT NUMBER := 8; -- Approval time out
22 --- Seeded phase level workflow status codes
23 G_RT_TIME_OUT CONSTANT VARCHAR2(30) := 'TIME_OUT' ; -- Time Out
24 G_RT_ABORTED CONSTANT VARCHAR2(30) := 'ABORTED' ; -- Aborted
25 --- Seeded Attachment Status
26 G_SUBMITTED_FOR_APPROVAL CONSTANT VARCHAR2(30) := 'SUBMITTED_FOR_APPROVAL';
27 G_SUBMITTED_FOR_REVIEW CONSTANT VARCHAR2(30) := 'SUBMITTED_FOR_REVIEW';
28 G_PENDING_CHANGE CONSTANT VARCHAR2(30) := 'PENDING_CHANGE';
29 G_APPROVED CONSTANT VARCHAR2(30) := 'APPROVED';
30 G_REJECTED CONSTANT VARCHAR2(30) := 'REJECTED';
31 G_REVIEWED CONSTANT VARCHAR2(30) := 'REVIEWED';
32 /********************************************************************
33 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
34 * Write_Debug
35 * Parameters IN :
36 * Parameters OUT:
37 * Purpose : These procedures are for test and debug
38 *********************************************************************/
39 -- Open_Debug_Session
40 Procedure Open_Debug_Session
41 ( p_output_dir IN VARCHAR2 := NULL
42 , p_file_name IN VARCHAR2 := NULL
43 )
44 IS
45 l_found NUMBER := 0;
46 l_utl_file_dir VARCHAR2(2000);
47
48 BEGIN
49
50 IF p_output_dir IS NOT NULL THEN
51 g_output_dir := p_output_dir ;
52
53 END IF ;
54
55 IF p_file_name IS NOT NULL THEN
56 g_debug_filename := p_file_name ;
57 END IF ;
58
59 IF g_output_dir IS NULL
60 THEN
61
62 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
63
64 END IF;
65
66 select value
67 INTO l_utl_file_dir
68 FROM v$parameter
69 WHERE name = 'utl_file_dir';
70
71 l_found := INSTR(l_utl_file_dir, g_output_dir);
72
73 IF l_found = 0
74 THEN
75 RETURN;
76 END IF;
77
78 g_debug_file := utl_file.fopen( g_output_dir
79 , g_debug_filename
80 , 'w');
81 g_debug_flag := TRUE ;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
86 g_debug_flag := FALSE;
87
88 END Open_Debug_Session ;
89
90 -- Close Debug_Session
91 Procedure Close_Debug_Session
92 IS
93 BEGIN
94 IF utl_file.is_open(g_debug_file)
95 THEN
96 utl_file.fclose(g_debug_file);
97 END IF ;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
102 g_debug_flag := FALSE;
103
104 END Close_Debug_Session ;
105
106 -- Test Debug
107 Procedure Write_Debug
108 ( p_debug_message IN VARCHAR2 )
109 IS
110 BEGIN
111
112 IF utl_file.is_open(g_debug_file)
113 THEN
114 utl_file.put_line(g_debug_file, p_debug_message);
115 END IF ;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
120 g_debug_flag := FALSE;
121
122 END Write_Debug;
123
124
125 /* This procedure will be called when a workflow is cancelled at any stage
126 of its lifecycle. When a document review/ approval is cancelled the
127 status of the document should be reverted to its previous state, the
128 state it was in, before it was submitted for review/ approval resp.
129 */
130 Procedure Cancel_Review_Approval(
131 p_api_version IN NUMBER
132 ,p_change_id IN NUMBER
133 ,x_return_status OUT NOCOPY VARCHAR2 --
134 ,x_msg_count OUT NOCOPY NUMBER --
135 ,x_msg_data OUT NOCOPY VARCHAR2 --
136 )
137 IS
138 cursor C IS
139 select attachment_id, previous_status,
140 decode(datatype_id, 8, family_id, source_document_id) document_id,
141 repository_id, created_by
142 from eng_attachment_changes
143 where change_id = p_change_id;
144
145 l_attachment_id NUMBER;
146 l_prev_status VARCHAR2(100);
147 l_document_id NUMBER;
148 l_repository_id NUMBER;
149 l_created_by NUMBER;
150 l_fnd_user_id NUMBER ;
151 l_fnd_login_id NUMBER ;
152
153
154 BEGIN
155 -- Initialize API return status to success
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158 l_fnd_user_id :=TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
159 l_fnd_login_id :=TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
160
161 OPEN C;
162 LOOP
163 FETCH C INTO l_attachment_id, l_prev_status,
164 l_document_id, l_repository_id, l_created_by;
165 EXIT WHEN C%NOTFOUND;
166
167 dom_attachment_util_pkg.Change_Status(
168 p_Attached_document_id => l_attachment_id
169 , p_Document_id => l_document_id
170 , p_Repository_id => l_repository_id
171 , p_Status => l_prev_status
172 , p_submitted_by => l_created_by
173 , p_last_updated_by => l_fnd_user_id
174 , p_last_update_login => l_fnd_login_id
175 );
176
177 --UPDATE fnd_attached_documents
178 --SET status = l_prev_status,
179 -- last_update_date = sysdate,
180 -- last_updated_by = l_fnd_user_id,
181 -- last_update_login = l_fnd_login_id
182 --WHERE attached_document_id = l_attachment_id;
183
184 END LOOP;
185 CLOSE C;
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 x_return_status := FND_API.G_RET_STS_ERROR;
190
191 END Cancel_Review_Approval;
192
193
194 Procedure Update_Attachment_Status
195 (
196 p_api_version IN NUMBER --
197 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
198 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
199 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
200 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
201 ,p_output_dir IN VARCHAR2 := NULL --
202 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Update_Attachment_Status.log'
203 ,x_return_status OUT NOCOPY VARCHAR2 --
204 ,x_msg_count OUT NOCOPY NUMBER --
205 ,x_msg_data OUT NOCOPY VARCHAR2 --
206 ,p_change_id IN NUMBER -- header's change_id
207 ,p_workflow_status IN VARCHAR2 -- workflow status
208 ,p_approval_status IN NUMBER -- approval status
209 ,p_api_caller IN VARCHAR2 DEFAULT 'UI'
210 )
211 IS
212
213 l_attachment_id number;
214 l_prev_status VARCHAR2(30);
215 l_attach_status VARCHAR2(30);
216 l_source_document_id NUMBER;
217 l_repository_id NUMBER;
218 l_category_id NUMBER;
219 l_dm_document_id NUMBER;
220 l_source_media_id NUMBER;
221 l_file_name VARCHAR2(2048);
222 l_datatype_id NUMBER;
223 l_created_by NUMBER;
224 l_fnd_user_id NUMBER :=TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
225 l_fnd_login_id NUMBER :=TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
226 l_doc_id NUMBER;
227
228 cursor C IS
229 select attachment_id, previous_status, source_document_id, repository_id,
230 category_id, family_id, source_media_id, file_name,
231 datatype_id, created_by
232 from eng_attachment_changes
233 where change_id = p_change_id;
234
235
236 l_api_name CONSTANT VARCHAR2(30) := 'Update_Attachment_Status';
237 l_api_version CONSTANT NUMBER := 1.0;
238 l_return_status VARCHAR2(1);
239 l_msg_count NUMBER;
240 l_msg_data VARCHAR2(2000);
241 l_update_approval_status BOOLEAN;
242
243 BEGIN
244 -- Standard Start of API savepoint
245 SAVEPOINT Update_Attachment_Status;
246
247 -- insert into swarb values(2, 'API caller ', p_api_caller);
248 -- commit;
249
250 -- following temp code to test the debug messages
251 -- Standard call to check for call compatibility
252 IF NOT FND_API.Compatible_API_Call ( l_api_version
253 ,p_api_version
254 ,l_api_name
255 ,G_PKG_NAME )
256 THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 END IF;
259 -- Initialize message list if p_init_msg_list is set to TRUE.
260 IF FND_API.to_Boolean( p_init_msg_list ) THEN
261 FND_MSG_PUB.initialize;
262 END IF ;
263 -- For Test/Debug
264 IF FND_API.to_Boolean( p_debug ) THEN
265 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
266 END IF ;
267
268 -- insert into swarb values(0, 'FND_USER', to_char(l_fnd_user_id));
269 -- commit;
270
271 -- Write debug message if debug mode is on
272 IF g_debug_flag THEN
273 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Update_Attachment_Status log');
274 Write_Debug('-----------------------------------------------------');
275 Write_Debug('p_change_id : ' || p_change_id );
276 Write_Debug('p_approval_status : ' || p_approval_status );
277 Write_Debug('-----------------------------------------------------');
278 Write_Debug('Initializing return status... ' );
279 END IF ;
280 -- Initialize API return status to success
281 x_return_status := FND_API.G_RET_STS_SUCCESS;
282
283
284 -- Real code starts here -----------------------------------
285 -- FND_PROFILE package is not available for workflow (WF),
286 -- therefore manually set WHO column values
287
288 -- insert into swarb values(1, to_char(p_change_id), p_workflow_status);
289 -- commit;
290
291 -- FND_PROFILE package is not available for workflow (WF),
292 -- therefore manually set WHO column values
293
294 IF p_api_caller = 'WF' THEN
295 l_fnd_user_id := G_ENG_WF_USER_ID;
296 l_fnd_login_id := G_ENG_WF_LOGIN_ID;
297 ELSIF p_api_caller = 'CP' THEN
298 l_fnd_user_id := G_ENG_CP_USER_ID;
299 l_fnd_login_id := G_ENG_CP_LOGIN_ID;
300 END IF;
301
302 --insert into swarb values(3, 'FND User', to_char(l_fnd_user_id));
303 --commit;
304
305 /* Bug: 4187851
306 When workflow is aborted, the status should remain as
307 Submitted For Approval. Removed code for abort/ time out
308 since this code since the code was changing the status
309 to previous status which is no longer true.
310 Status will only be changed to previous status when the
311 workflow in cancelled.
312
313 */ -- Bug: 4187851
314
315
316 Get_Attachment_Status(p_change_id, p_approval_status, l_attach_status);
317
318 -- insert into swarb values(4, 'After get attachment', l_attach_status);
319 -- commit;
320
321 IF g_debug_flag THEN
322 Write_Debug('p_approval_status:'||p_approval_status);
323 Write_Debug('l_attach_status:'||l_attach_status);
324 END IF ;
325
326 OPEN C;
327 LOOP
328 FETCH C INTO l_attachment_id, l_prev_status,
329 l_source_document_id, l_repository_id,
330 l_category_id, l_dm_document_id,
331 l_source_media_id, l_file_name,
332 l_datatype_id, l_created_by;
333 EXIT WHEN C%NOTFOUND;
334
335 -- insert into swarb values(5, to_char(l_attachment_id), l_prev_status);
336 -- commit;
337
338 l_update_approval_status:=false;
339
340 -- Only change the status when the approval status is "Approved"
341 -- or rejected.
342 -- If status is "Approvel Requested", document att. status = "Submitted
343 -- For Approval"
344 -- If status is "Approved", document attachment status = Approved
345 -- If status is "Rejected", document attachment status = "Rejected"
346 -- If workflow Status is "Promoted/ Demoted" then do not change the
347 -- attachment status
348
349 --insert into swarb values(.1, 'Approval_status', p_approval_status);
350 --commit;
351
352 IF (p_approval_status=''||G_ENG_APPR_APPROVED
353 OR p_approval_status =''|| G_ENG_APPR_REJECTED
354 OR p_approval_status =''|| G_ENG_APPR_REQUESTED) THEN
355
356 IF (l_attach_status is not null OR length(l_attach_status)>0) THEN
357
358 --insert into swarb values(.2, 'In approval status',to_char(l_attach_status));
359 --commit;
360
361 l_update_approval_status:=true;
362 END IF;
363
364 END IF; -- IF (p_approval_status=''||G_ENG_APPR_APPROVED
365
366 --insert into swarb values(.1, 'After Approval_status', null);
367 --commit;
368
369 IF (l_update_approval_status) THEN
370
371 -- If it is a webservices file, then pass the dm_document_id
372 -- else fnd_document_id
373 if (l_datatype_id = 8) then
374 l_doc_id := l_dm_document_id;
375 else
376 l_doc_id := l_source_document_id;
377 end if;
378
379 dom_attachment_util_pkg.Change_Status(
380 p_Attached_document_id => l_attachment_id
381 , p_Document_id => l_doc_id
382 , p_Repository_id => l_repository_id
383 , p_Status => l_attach_status
384 , p_submitted_by => l_created_by
385 , p_last_updated_by => l_fnd_user_id
386 , p_last_update_login => l_fnd_login_id
387 );
388
389 END IF; -- IF (l_update_approval_status) THEN
390 END LOOP;
391 CLOSE C;
392
393
394 Project_deliverable_tracking(P_CHANGE_ID => p_change_id,
395 P_ATTACHMENT_ID => l_attachment_id,
396 P_DOCUMENT_ID => l_source_document_id,
397 P_ATTACH_STATUS => l_attach_status,
398 P_CATEGORY_ID => l_category_id,
399 P_REPOSITORY_ID => l_repository_id,
400 P_DM_DOCUMENT_ID => l_dm_document_id,
401 P_SOURCE_MEDIA_ID => l_source_media_id,
402 P_FILE_NAME => l_file_name,
403 P_CREATED_BY => l_created_by,
404 X_RETURN_STATUS => x_return_status,
405 X_MSG_COUNT => x_msg_count,
406 X_MSG_DATA => x_msg_data
407 );
408
409
410
411 -- insert into swarb values(100, 'After project call', null);
412 -- commit;
413
414 -- Standard ending code ------------------------------------------------
415
416 FND_MSG_PUB.Count_And_Get
417 ( p_count => x_msg_count,
418 p_data => x_msg_data );
419
420 IF g_debug_flag THEN
421 Write_Debug('Msg Data' || x_msg_data);
422 Write_Debug('Finish. End Of Proc') ;
423 Close_Debug_Session ;
424 END IF ;
425
426 EXCEPTION
427 WHEN FND_API.G_EXC_ERROR THEN
428 ROLLBACK TO Update_Attachment_Status;
429 x_return_status := FND_API.G_RET_STS_ERROR;
430 FND_MSG_PUB.Count_And_Get
431 ( p_count => x_msg_count
432 ,p_data => x_msg_data );
433 IF g_debug_flag THEN
434 Write_Debug('Msg Data' || x_msg_data);
435 Write_Debug('Rollback and Finish with expected error.') ;
436 Close_Debug_Session ;
437 END IF ;
438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
439 ROLLBACK TO Update_Attachment_Status;
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 FND_MSG_PUB.Count_And_Get
442 ( p_count => x_msg_count
443 ,p_data => x_msg_data );
444 IF g_debug_flag THEN
445 Write_Debug('Msg Data' || x_msg_data);
446 Write_Debug('Rollback and Finish with unexpected error.') ;
447 Close_Debug_Session ;
448 END IF ;
449 WHEN OTHERS THEN
450 ROLLBACK TO Update_Attachment_Status;
451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
452 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
453 THEN
454 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
455 END IF;
456 FND_MSG_PUB.Count_And_Get
457 ( p_count => x_msg_count
458 ,p_data => x_msg_data );
459 IF g_debug_flag THEN
460 Write_Debug('Msg Data' || x_msg_data);
461 Write_Debug('Rollback and Finish with other error.') ;
462 Close_Debug_Session ;
463 END IF ;
464
465 END Update_Attachment_Status;
466
467
468 Procedure Project_deliverable_tracking(
469 p_change_id IN NUMBER
470 ,p_attachment_id IN NUMBER
471 ,p_document_id IN NUMBER
472 ,p_attach_status IN VARCHAR2
473 ,p_category_id IN NUMBER
474 ,p_repository_id IN NUMBER
475 ,p_dm_document_id IN NUMBER
476 ,p_source_media_id IN NUMBER
477 ,p_file_name IN VARCHAR2
478 ,p_created_by IN NUMBER
479 ,x_return_status OUT NOCOPY VARCHAR2
480 ,x_msg_count OUT NOCOPY NUMBER
481 ,x_msg_data OUT NOCOPY VARCHAR2
482 )
483 IS
484
485 cursor c_get_change_project_info IS
486 select project_id, task_id, organization_id
487 from eng_engineering_changes
488 where change_id = p_change_id;
489
490 cursor c_get_curr_oper_unit_id(p_orgid NUMBER) IS
491 SELECT OPERATING_UNIT
492 FROM ORG_ORGANIZATION_DEFINITIONS
493 WHERE ORGANIZATION_ID = p_orgid;
494
495 l_project_id NUMBER;
496 l_task_id NUMBER;
497 l_base_change_mgmt_type_code VARCHAR2(30);
498 l_existing_comp_percent NUMBER;
499 l_percent_complete NUMBER;
500 l_task_status VARCHAR2(10);
501 l_fnd_user_id NUMBER;
502 l_responsibility_id NUMBER;
503 l_fnd_login_id NUMBER;
504 l_new_attached_doc_id NUMBER;
505 l_org_id NUMBER;
506 l_operating_unit_id NUMBER;
507
508 BEGIN
509
510 -- insert into swarb values(7.0, 'In project deliverable', to_char(p_change_id));
511 -- commit;
512
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514
515 -- Cursor to get project and task based on the change_id
516 OPEN c_get_change_project_info;
517 FETCH c_get_change_project_info into l_project_id, l_task_id, l_org_id;
518 CLOSE c_get_change_project_info;
519
520 -- insert into swarb values(7, to_char(l_project_id), to_char(l_task_id));
521 -- commit;
522
523 IF g_debug_flag THEN
524 Write_Debug('l_task_id:'||to_char(l_task_id));
525 END IF ;
526
527 -- Execute following only for attachment approval
528 SELECT ecot.base_change_mgmt_type_code INTO l_base_change_mgmt_type_code
529 FROM eng_engineering_changes eec,eng_change_order_types ecot
530 WHERE eec.change_id = p_change_id
531 AND ecot.change_order_type_id = eec.change_order_type_id;
532
533 -- insert into swarb values(8, 'change orde type', l_base_change_mgmt_type_code);
534 -- commit;
535
536
537 IF (l_base_change_mgmt_type_code = 'ATTACHMENT_APPROVAL') THEN
538
539 IF ( l_task_id IS NOT NULL) THEN
540
541 IF g_debug_flag THEN
542 Write_Debug('l_task_id is not null..');
543 END IF ;
544
545 l_fnd_user_id := fnd_global.user_id;
546 IF g_debug_flag THEN
547 Write_Debug('l_fnd_user_id:'|| to_char(l_fnd_user_id));
548 END IF ;
549
550 BEGIN
551
552 -- insert into swarb values(8.7, 'Inside begin', null);
553 -- commit;
554
555 SELECT max(completed_percentage) into l_existing_comp_percent
556 FROM PA_TASK_PROGRESS_AMG_V
557 WHERE project_id = l_project_id and task_id = l_task_id ;
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN
560 l_existing_comp_percent := 0;
561 END;
562
563 -- insert into swarb values(9, 'Completed %', to_char(l_existing_comp_percent));
564 -- commit;
565
566 IF g_debug_flag THEN
567 Write_Debug('completed_percentage:'|| to_char(l_existing_comp_percent));
568 END IF ;
569
570 -- Commenting out the sql since a guest user from workflow
571 --may not have Project Super User responsibility
572
573 -- SELECT responsibility_id INTO l_responsibility_id
574 -- FROM pa_user_resp_v
575 -- WHERE user_id=l_fnd_user_id
576 -- AND responsibility_name = 'Project Super User';
577
578 SELECT responsibility_id INTO l_responsibility_id
579 FROM fnd_responsibility
580 WHERE responsibility_key = 'PA_PRM_PROJ_SU';
581
582 -- insert into swarb values(10, 'Responsibility id', l_responsibility_id);
583 -- commit;
584
585 IF g_debug_flag THEN
586 Write_Debug('l_responsibility_id:'||to_char(l_responsibility_id));
587 Write_Debug('p_attachment_id:'||to_char(p_attachment_id));
588 Write_Debug('p_attach_status:'||p_attach_status);
589 END IF ;
590
591 IF(p_attach_status = G_SUBMITTED_FOR_APPROVAL
592 AND p_attachment_id IS NOT NULL) THEN
593
594 IF g_debug_flag THEN
595 Write_Debug('submitted for approval!');
596 END IF ;
597
598 l_fnd_login_id := fnd_global.login_id;
599
600 IF g_debug_flag THEN
601 Write_Debug('p_document_id:'||to_char(p_document_id));
602 Write_Debug('l_fnd_login_id:'||to_char(l_fnd_login_id));
603 END IF ;
604
605 -- First creating a record in fnd_attached_documents with the
606 -- task id
607
608 dom_attachment_util_pkg.Create_Attachment(
609 x_Attached_document_id => l_new_attached_doc_id
610 , p_Document_id => p_document_id
611 , p_Entity_name => 'PA_TASKS'
612 , p_Pk1_value => l_task_id
613 , p_category_id => p_category_id
614 , p_repository_id => p_repository_id
615 , p_version_id => p_dm_document_id
616 , p_family_id => p_source_media_id
617 , p_file_name => p_file_name
618 , p_created_by => l_fnd_user_id
619 , p_last_update_login => l_fnd_login_id
620 );
621
622 -- Then changing the status of the attachment
623
624 Dom_Attachment_util_pkg.Change_Status(
625 p_Attached_document_id => l_new_attached_doc_id
626 , p_Document_id => p_document_id
627 , p_Repository_id => p_repository_id
628 , p_Status => p_attach_status
629 , p_submitted_by => p_created_by
630 , p_last_updated_by => l_fnd_user_id
631 , p_last_update_login => l_fnd_login_id
632 );
633
634 -- insert into swarb values(12, 'After DOM API', to_char(l_new_attached_doc_id));
635 -- commit;
636
637 l_percent_complete := 50;
638 l_task_status := '125';
639
640 ELSIF(p_attach_status = G_REJECTED AND p_attachment_id IS NOT NULL) THEN
641
642 l_percent_complete := l_existing_comp_percent;
643 l_task_status := '125';
644
645 ELSIF(p_attach_status = G_APPROVED AND p_attachment_id IS NOT NULL) THEN
646
647 l_percent_complete := 100;
648 l_task_status := '127';
649
650 END IF; -- IF (p_attach_status = G_SUBMITTED...)
651 --bug 5365842 fix begins
652 -- following PA API call to change the task status to In progress
653 /* commented out and replaced with PA_MOAC_UTILS.MO_INIT_SET_CONTEXT for bug 5365842
654 PA_INTERFACE_UTILS_PUB.SET_GLOBAL_INFO(
655 P_API_VERSION_NUMBER => 1,
656 P_RESPONSIBILITY_ID => l_responsibility_id ,
657 P_USER_ID => l_fnd_user_id,
658 P_RETURN_STATUS => x_return_status,
659 P_MSG_COUNT => x_msg_count,
660 P_MSG_DATA => x_msg_data);*/
661
662 OPEN c_get_curr_oper_unit_id(l_org_id);
663 FETCH c_get_curr_oper_unit_id INTO l_operating_unit_id;
664 CLOSE c_get_curr_oper_unit_id;
665
666 /*as per the base bug 5372737 we should pass operating unit id for orgid
667 and product_code as PA*/
668 PA_MOAC_UTILS.MO_INIT_SET_CONTEXT
669 (
670 p_org_id => l_operating_unit_id ,
671 p_product_code => 'PA',
672 p_msg_count => x_msg_count,
673 p_msg_data => x_msg_data,
674 p_return_status => x_return_status
675 );
676 --bug 5365842 fix ends
677 PA_STATUS_PUB.UPDATE_PROGRESS(
678 P_API_VERSION_NUMBER => 1 ,
679 P_RETURN_STATUS => x_return_status,
680 P_MSG_COUNT => x_msg_count,
681 P_MSG_DATA => x_msg_data,
682 P_PROJECT_ID => l_project_id,
683 P_TASK_ID => l_task_id,
684 P_AS_OF_DATE => sysdate,
685 P_STRUCTURE_TYPE => 'WORKPLAN',
686 P_PERCENT_COMPLETE => l_percent_complete,
687 p_task_status => l_task_status);
688
689
690 -- insert into swarb values(17, 'After PA APIs - Approved', x_return_status);
691 -- commit;
692
693 END IF; -- if Task is not null , means task is assigned to the approval
694 END IF; -- if base change mgmt type code is attachment approval
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
699 Write_Debug('Msg Data' || x_msg_data);
700
701 END Project_Deliverable_Tracking;
702
703
704
705
706 Procedure Implement_Attachment_Change
707 (
708 p_api_version IN NUMBER --
709 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
710 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
711 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
712 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
713 ,p_output_dir IN VARCHAR2 := NULL --
714 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Implement_Attachment_Change.log'
715 ,x_return_status OUT NOCOPY VARCHAR2 --
716 ,x_msg_count OUT NOCOPY NUMBER --
717 ,x_msg_data OUT NOCOPY VARCHAR2 --
718 ,p_change_id IN NUMBER -- header's change_id
719 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
720 ,p_approval_status IN NUMBER -- approval status
721
722 )
723 IS
724 l_action_type varchar2(30);
725 l_attachment_id number;
726 l_source_document_id NUMBER;
727 l_entity_name VARCHAR2(40);
728 l_pk1_value VARCHAR2(100);
729 l_pk2_value VARCHAR2(100);
730 l_pk3_value VARCHAR2(100);
731 l_pk4_value VARCHAR2(100);
732 l_pk5_value VARCHAR2(100);
733 l_category_id NUMBER;
734 l_file_name VARCHAR2(255);
735 l_dest_document_id NUMBER;
736 l_dest_version_label VARCHAR2(255);
737 l_dest_path VARCHAR2(1000);
738 l_new_file_name VARCHAR2(255);
739 l_new_description VARCHAR2(255);
740 l_new_category_id NUMBER;
741
742 l_fnd_user_id NUMBER;
743 l_fnd_login_id NUMBER;
744
745 l_document_id NUMBER;
746 l_row_id VARCHAR2(2000);
747 l_attach_status VARCHAR2(30);
748 l_datatype_id NUMBER;
749 l_attach_doc_id NUMBER;
750 l_media_id NUMBER;
751 l_created_by NUMBER;
752 l_last_update_login NUMBER;
753 l_repository_id NUMBER;
754 l_dm_document_id NUMBER;
755 l_family_id NUMBER;
756 l_dm_type VARCHAR2(30);
757 l_protocol VARCHAR2(30);
758
759 cursor C IS
760 select a.action_type, a.attachment_id, a.source_document_id,
761 a.entity_name, a.pk1_value, a.pk2_value, a.pk3_value,
762 a.pk4_value, a.pk5_value, a.category_id, a.dest_version_label,
763 a.file_name, a.new_file_name, a.new_description,
764 a.new_category_id, a.created_by, a.last_update_login,
765 a.repository_id,
766 decode(b.protocol, 'WEBDAV', -1, a.family_id) family_id,
767 a.dm_type, b.protocol
768 from eng_attachment_changes a, dom_repositories b
769 where change_id = p_change_id
770 and revised_item_sequence_id = p_rev_item_seq_id
771 and a.repository_id = b.id;
772
773
774 l_api_name CONSTANT VARCHAR2(30) := 'Implement_Attachment_Change';
775 l_api_version CONSTANT NUMBER := 1.0;
776 l_return_status VARCHAR2(1);
777 l_msg_count NUMBER;
778 l_msg_data VARCHAR2(2000);
779 l_message VARCHAR2(4000);
780
781 BEGIN
782 -- Standard Start of API savepoint
783 SAVEPOINT Implement_Attachment_Change;
784
785 -- Standard call to check for call compatibility
786 IF NOT FND_API.Compatible_API_Call ( l_api_version
787 ,p_api_version
788 ,l_api_name
789 ,G_PKG_NAME )
790 THEN
791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
792 END IF;
793
794 -- Initialize message list if p_init_msg_list is set to TRUE.
795 IF FND_API.to_Boolean( p_init_msg_list ) THEN
796 FND_MSG_PUB.initialize;
797 END IF ;
798 -- For Test/Debug
799 IF FND_API.to_Boolean( p_debug ) THEN
800 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
801 END IF ;
802
803 -- Write debug message if debug mode is on
804 IF g_debug_flag THEN
805 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Implement_Attachment_Change log');
806 Write_Debug('-----------------------------------------------------');
807 Write_Debug('p_change_id : ' || p_change_id );
808 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
809 Write_Debug('-----------------------------------------------------');
810 Write_Debug('Initializing return status... ' );
811 END IF ;
812 -- Initialize API return status to success
813 x_return_status := FND_API.G_RET_STS_SUCCESS;
814
815
816 -- Real code starts here -----------------------------------------------
817
818 -- get values for fnd_user and fnd_login
819 SELECT fnd_global.user_id, fnd_global.login_id
820 INTO l_fnd_user_id, l_fnd_login_id
821 FROM dual;
822
823 -- First check if there are floating versions in the CO to be implemented.
824 -- if there are flaoting version, check its change policy and ensure that
825 -- it is not under CO Required. If it is CO required, the implementation
826 -- should fail.
827
828 Validate_floating_version (
829 p_api_version => 1
830 ,p_change_id => p_change_id
831 ,p_rev_item_seq_id => p_rev_item_seq_id
832 , x_return_status => x_return_status
833 , x_msg_count => x_msg_count
834 , x_msg_data => x_msg_data
835 );
836
837
838 -- insert into swarb values(0, 'After fnd_user', to_char(p_approval_status));
839 -- commit;
840
841 Get_Attachment_Status(p_change_id, p_approval_status, l_attach_status);
842
843 -- insert into swarb values(-1, 'After get_attachment_status', l_attach_status);
844 -- commit;
845
846 OPEN C;
847 LOOP
848
849 FETCH C
850 INTO l_action_type, l_attachment_id, l_source_document_id, l_entity_name,
851 l_pk1_value, l_pk2_value, l_pk3_value, l_pk4_value, l_pk5_value,
852 l_category_id, l_dest_version_label, l_file_name, l_new_file_name,
853 l_new_description, l_new_category_id, l_created_by,
854 l_last_update_login, l_repository_id, l_family_id, l_dm_type,
855 l_protocol;
856
857 EXIT WHEN C%NOTFOUND;
858
859 -- insert into swarb values(1, 'Inside loop ->', to_char(l_source_document_id));
860 -- commit;
861
862 -- Get the document_id for the attachment
863 SELECT document_id INTO l_document_id
864 FROM fnd_documents
865 WHERE document_id = l_source_document_id;
866
867 -- insert into swarb values(1, 'attachment not null->', to_char(l_document_id));
868 -- commit;
869
870 IF l_document_id IS NULL THEN
871 l_message := 'ENG_DETACH_IMP_ERROR';
872 FND_MESSAGE.Set_Name('ENG', l_message);
873 FND_MESSAGE.Set_Token('FILE_NAME', l_file_name);
874 FND_MSG_PUB.Add;
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 END IF;
877
878
879 IF l_action_type = 'ATTACH' THEN
880
881 -- Swarnali - no longer needed with the new DOM package
882
883 -- select fnd_attached_documents_s.nextval
884 -- into l_attachment_id from dual;
885
886 -- insert into swarb values(2, 'inside attach ->', to_char(l_attachment_id));
887 -- commit;
888
889 dom_attachment_util_pkg.Attach(
890 x_Attached_document_id => l_attachment_id
891 , p_Document_id => l_source_document_id
892 , p_Entity_name => l_entity_name
893 , p_Pk1_value => l_pk1_value
894 , p_Pk2_value => l_pk2_value
895 , p_Pk3_value => l_pk3_value
896 , p_Pk4_value => l_pk4_value
897 , p_Pk5_value => l_pk5_value
898 , p_category_id => l_category_id
899 , p_created_by => l_created_by
900 , p_last_update_login => l_last_update_login
901 );
902
903 elsif l_action_type = 'DETACH' then
904
905 -- Call DOM API to Detach attachments
906 dom_attachment_util_pkg.Detach(
907 p_Attached_document_id => l_attachment_id
908 );
909
910 elsif l_action_type = 'CHANGE_VERSION_LABEL' then
911
912 dom_attachment_util_pkg.Change_Version(
913 p_Attached_document_id => l_attachment_id
914 , p_Document_id => l_source_document_id
915 , p_last_updated_by => l_created_by
916 , p_last_update_login => l_last_update_login
917 );
918
919 -- In modify action, you can change values for file_name,
920 -- desc and category
921 elsif l_action_type = 'MODIFY' then
922
923 dom_attachment_util_pkg.Update_Document(
924 p_Attached_document_id => l_attachment_id
925 , p_FileName => l_new_file_name
926 , p_Description => l_new_description
927 , p_Category => l_new_category_id
928 , p_last_updated_by => l_created_by
929 , p_last_update_login => l_last_update_login
930 );
931
932 end if;
933
934 -- Updating the status of the attached document
935
936 -- If it is a floating version file then do not change its status
937 -- l_family_id stores the dm_document_id information. If l_family_id
938 -- = 0, that means it is a floating version
939
940 if (l_action_type <> 'DETACH' or l_dm_type <> 'FOLDER') then
941 if (l_family_id <> 0) then -- <> 0 is not a floating version
942
943 -- If protocol is WEBSERVICES then pass the DM_DOCUMENT_ID instead
944 -- of FND_DOCUMENT_ID which is stored in source_document_id
945
946 if (l_protocol = 'WEBSERVICES') then
947 l_source_document_id := l_family_id;
948 end if;
949
950 dom_attachment_util_pkg.Change_Status(
951 p_attached_document_id => l_attachment_id
952 , p_document_id => l_source_document_id
953 , p_repository_id => l_repository_id
954 , p_status => l_attach_status
955 , p_submitted_by => l_created_by
956 , p_last_updated_by => l_fnd_user_id
957 , p_last_update_login => l_fnd_login_id
958 );
959 end if;
960 end if;
961
962 end loop;
963 close C;
964
965 -- Standard ending code ------------------------------------------------
966 FND_MSG_PUB.Count_And_Get
967 ( p_count => x_msg_count,
968 p_data => x_msg_data );
969
970 IF g_debug_flag THEN
971 Write_Debug('Finish. End Of Proc') ;
972 Close_Debug_Session ;
973 END IF ;
974
975 EXCEPTION
976 WHEN FND_API.G_EXC_ERROR THEN
977 ROLLBACK TO Implement_Attachment_Change;
978 x_return_status := FND_API.G_RET_STS_ERROR;
979 FND_MSG_PUB.Count_And_Get
980 ( p_count => x_msg_count
981 ,p_data => x_msg_data );
982 IF g_debug_flag THEN
983 Write_Debug('Rollback and Finish with expected error.') ;
984 Close_Debug_Session ;
985 END IF ;
986 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
987 ROLLBACK TO Implement_Attachment_Change;
988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 FND_MSG_PUB.Count_And_Get
990 ( p_count => x_msg_count
991 ,p_data => x_msg_data );
992 IF g_debug_flag THEN
993 Write_Debug('Rollback and Finish with unexpected error.') ;
994 Close_Debug_Session ;
995 END IF ;
996 WHEN OTHERS THEN
997 ROLLBACK TO Implement_Attachment_Change;
998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
999 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1000 THEN
1001 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1002 END IF;
1003 FND_MSG_PUB.Count_And_Get
1004 ( p_count => x_msg_count
1005 ,p_data => x_msg_data );
1006 IF g_debug_flag THEN
1007 Write_Debug('Rollback and Finish with other error.') ;
1008 Close_Debug_Session ;
1009 END IF ;
1010
1011 END Implement_Attachment_Change;
1012
1013
1014 -- This API is DEPRICATED. Please use DOM_ATTACHMENT_UTIL_PKG.copy_attachments
1015 Procedure Copy_Attachment (
1016 p_api_version IN NUMBER --
1017 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1018 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1019 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1020 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1021 ,p_output_dir IN VARCHAR2 := NULL --
1022 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Copy_Attachment.log'
1023 ,x_return_status OUT NOCOPY VARCHAR2 --
1024 ,x_msg_count OUT NOCOPY NUMBER --
1025 ,x_msg_data OUT NOCOPY VARCHAR2
1026 ,x_new_attachment_id OUT NOCOPY NUMBER
1027 ,p_source_attachment_id IN NUMBER -- source attached document id
1028 ,p_source_status IN VARCHAR2 -- source attachment status
1029 ,p_dest_entity_name IN VARCHAR2 -- destination entity name
1030 ,p_dest_pk1_value IN VARCHAR2 -- destination pk1 value
1031 ,p_dest_pk2_value IN VARCHAR2 -- destination pk2 value
1032 ,p_dest_pk3_value IN VARCHAR2 -- destination pk3 value
1033 ,p_dest_pk4_value IN VARCHAR2 -- destination pk4 value
1034 ,p_dest_pk5_value IN VARCHAR2 -- destination pk5 value
1035 )
1036 IS
1037 l_datatype_id NUMBER;
1038 l_category_id NUMBER;
1039 l_source_doc_id NUMBER;
1040 l_file_name VARCHAR2(255);
1041 l_language VARCHAR2(30);
1042 l_description VARCHAR2(255);
1043 l_media_id NUMBER;
1044 l_dm_node NUMBER;
1045 l_dm_folder_path VARCHAR2(1000);
1046 l_dm_type VARCHAR2(30);
1047 l_dm_document_id VARCHAR2(255);
1048 l_dm_version_number VARCHAR2(4000);
1049 l_row_id varchar2(2000);
1050 l_seq_num number := 1;
1051 l_auto_add_flag varchar2(1) := 'N';
1052 l_attached_doc_id number;
1053 l_doc_id NUMBER;
1054 l_security_type number := 1;
1055 l_security_id number;
1056 l_publish_flag varchar2(1) := 'Y';
1057 l_usage_type varchar2(1) := 'O';
1058 l_fnd_user_id NUMBER :=TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
1059 l_fnd_login_id NUMBER :=TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
1060 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachment';
1061 l_api_version CONSTANT NUMBER := 1.0;
1062 l_return_status VARCHAR2(1);
1063 l_msg_count NUMBER;
1064 l_msg_data VARCHAR2(2000);
1065 BEGIN
1066 -- Standard Start of API savepoint
1067 SAVEPOINT Copy_Attachment;
1068 -- Standard call to check for call compatibility
1069 IF NOT FND_API.Compatible_API_Call ( l_api_version
1070 ,p_api_version
1071 ,l_api_name
1072 ,G_PKG_NAME )
1073 THEN
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076 -- Initialize message list if p_init_msg_list is set to TRUE.
1077 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1078 FND_MSG_PUB.initialize;
1079 END IF ;
1080 -- For Test/Debug
1081 IF FND_API.to_Boolean( p_debug ) THEN
1082 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1083 END IF ;
1084
1085 -- Write debug message if debug mode is on
1086 IF g_debug_flag THEN
1087 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Implement_Attachment_Change log');
1088 Write_Debug('-----------------------------------------------------');
1089 Write_Debug('p_source_attachment_id : ' || p_source_attachment_id );
1090 Write_Debug('p_source_status : ' || p_source_status );
1091 Write_Debug('p_dest_entity_name : ' || p_dest_entity_name );
1092 Write_Debug('p_dest_pk1_value : ' || p_dest_pk1_value );
1093 Write_Debug('p_dest_pk2_value : ' || p_dest_pk2_value );
1094 Write_Debug('p_dest_pk3_value : ' || p_dest_pk3_value );
1095 Write_Debug('p_dest_pk4_value : ' || p_dest_pk4_value );
1096 Write_Debug('p_dest_pk5_value : ' || p_dest_pk5_value );
1097 Write_Debug('-----------------------------------------------------');
1098 Write_Debug('Initializing return status... ' );
1099 END IF ;
1100 -- Initialize API return status to success
1101 x_return_status := FND_API.G_RET_STS_SUCCESS;
1102 -- Real code starts here -----------------------------------------------
1103 l_fnd_user_id := fnd_global.user_id; -- -1; -- Bug 3700111
1104 l_fnd_login_id := fnd_global.login_id; -- ''; -- Bug 3700111
1105 select userenv('LANG') into l_language from dual;
1106 select category_id, document_id into l_category_id, l_source_doc_id from fnd_attached_documents where attached_document_id = p_source_attachment_id;
1107 select datatype_id, security_type, dm_node, dm_folder_path, dm_type, dm_document_id, dm_version_number
1108 into l_datatype_id, l_security_type, l_dm_node, l_dm_folder_path, l_dm_type, l_dm_document_id, l_dm_version_number
1109 from fnd_documents where document_id = l_source_doc_id;
1110 select file_name, description, media_id into l_file_name, l_description, l_media_id from fnd_documents_tl where document_id = l_source_doc_id and language = userenv('LANG');
1111 select fnd_attached_documents_s.nextval
1112 into l_attached_doc_id
1113 from dual;
1114 fnd_attached_documents_pkg.Insert_Row(
1115 X_Rowid => l_row_id,
1116 X_attached_document_id => l_attached_doc_id,
1117 X_document_id => l_doc_id,
1118 X_creation_date => sysdate,
1119 X_created_by => l_fnd_user_id,
1120 X_last_update_date => sysdate,
1121 X_last_updated_by => l_fnd_user_id,
1122 X_last_update_login => l_fnd_login_id,
1123 X_seq_num => l_seq_num,
1124 X_entity_name => p_dest_entity_name,
1125 X_column1 => null,
1126 X_pk1_value => p_dest_pk1_value,
1127 X_pk2_value => p_dest_pk2_value,
1128 X_pk3_value => p_dest_pk3_value,
1129 X_pk4_value => p_dest_pk4_value,
1130 X_pk5_value => p_dest_pk5_value,
1131 X_automatically_added_flag => l_auto_add_flag,
1132 X_datatype_id => l_datatype_id,
1133 X_category_id => l_category_id,
1134 X_security_type => l_security_type,
1135 X_publish_flag => l_publish_flag,
1136 X_usage_type => l_usage_type,
1137 X_language => l_language,
1138 X_description => l_description,
1139 X_file_name => l_file_name,
1140 X_media_id => l_media_id,
1141 X_doc_attribute_Category => null,
1142 X_doc_attribute1 => null,
1143 X_doc_attribute2 => null,
1144 X_doc_attribute3 => null,
1145 X_doc_attribute4 => null,
1146 X_doc_attribute5 => null,
1147 X_doc_attribute6 => null,
1148 X_doc_attribute7 => null,
1149 X_doc_attribute8 => null,
1150 X_doc_attribute9 => null,
1151 X_doc_attribute10 => null,
1152 X_doc_attribute11 => null,
1153 X_doc_attribute12 => null,
1154 X_doc_attribute13 => null,
1155 X_doc_attribute14 => null,
1156 X_doc_attribute15 => null,
1157 X_create_doc => 'Y' -- Fix for 3762710
1158 );
1159 update fnd_attached_documents set category_id = l_category_id, status = p_source_status where attached_document_id = l_attached_doc_id;
1160 update fnd_documents set dm_node = l_dm_node, dm_folder_path = l_dm_folder_path, dm_type = l_dm_type, dm_document_id = l_dm_document_id, dm_version_number = l_dm_version_number where document_id = l_doc_id;
1161 x_new_attachment_id := l_attached_doc_id;
1162
1163 -- Standard ending code ------------------------------------------------
1164 FND_MSG_PUB.Count_And_Get
1165 ( p_count => x_msg_count,
1166 p_data => x_msg_data );
1167
1168 IF g_debug_flag THEN
1169 Write_Debug('Finish. End Of Proc') ;
1170 Close_Debug_Session ;
1171 END IF ;
1172
1173 EXCEPTION
1174 WHEN FND_API.G_EXC_ERROR THEN
1175 ROLLBACK TO Copy_Attachment;
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 FND_MSG_PUB.Count_And_Get
1178 ( p_count => x_msg_count
1179 ,p_data => x_msg_data );
1180 IF g_debug_flag THEN
1181 Write_Debug('Rollback and Finish with expected error.') ;
1182 Close_Debug_Session ;
1183 END IF ;
1184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1185 ROLLBACK TO Copy_Attachment;
1186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1187 FND_MSG_PUB.Count_And_Get
1188 ( p_count => x_msg_count
1189 ,p_data => x_msg_data );
1190 IF g_debug_flag THEN
1191 Write_Debug('Rollback and Finish with unexpected error.') ;
1192 Close_Debug_Session ;
1193 END IF ;
1194 WHEN OTHERS THEN
1195 ROLLBACK TO Copy_Attachment;
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1197 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1198 THEN
1199 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1200 END IF;
1201 FND_MSG_PUB.Count_And_Get
1202 ( p_count => x_msg_count
1203 ,p_data => x_msg_data );
1204 IF g_debug_flag THEN
1205 Write_Debug('Rollback and Finish with other error.') ;
1206 Close_Debug_Session ;
1207 END IF ;
1208
1209 END Copy_Attachment;
1210
1211 Procedure Get_Attachment_Status (
1212 p_change_id IN NUMBER
1213 ,p_header_status IN NUMBER
1214 ,x_attachment_status OUT NOCOPY VARCHAR2
1215 )
1216 IS
1217 l_change_order_type_id number;
1218 l_base_change_mgmt_type_code varchar2(30);
1219 BEGIN
1220 select change_order_type_id into l_change_order_type_id
1221 from eng_engineering_changes
1222 where change_id = p_change_id;
1223 select base_change_mgmt_type_code into l_base_change_mgmt_type_code
1224 from eng_change_order_types
1225 where change_order_type_id = l_change_order_type_id;
1226 if(l_base_change_mgmt_type_code = 'ATTACHMENT_APPROVAL') then
1227 if(p_header_status = G_ENG_APPR_REQUESTED) then
1228 x_attachment_status := G_SUBMITTED_FOR_APPROVAL;
1229 return;
1230 elsif(p_header_status = G_ENG_APPR_APPROVED) then
1231 x_attachment_status := G_APPROVED;
1232 return;
1233 elsif(p_header_status = G_ENG_APPR_REJECTED) then
1234 x_attachment_status := G_REJECTED;
1235 return;
1236 end if;
1237 elsif(l_base_change_mgmt_type_code = 'ATTACHMENT_REVIEW') then
1238 if(p_header_status = G_ENG_APPR_REQUESTED) then
1239 x_attachment_status := G_SUBMITTED_FOR_REVIEW;
1240 return;
1241 elsif(p_header_status = G_ENG_APPR_APPROVED) then
1242 x_attachment_status := G_REVIEWED;
1243 return;
1244 end if;
1245 elsif(l_base_change_mgmt_type_code = 'CHANGE_ORDER') then
1246 if(p_header_status = G_ENG_NOT_SUBMITTED) then
1247 x_attachment_status := G_APPROVED; -- x_attachment_status := G_PENDING_CHANGE;
1248 -- vamohan: Fix for 3471772: commented out previous line since for a CO, once att changes are implemented, approval status in FND_ATTACHED_DOCUMENTS should be nulled out
1249 return;
1250 elsif(p_header_status = G_ENG_APPR_APPROVED) then
1251 x_attachment_status := G_APPROVED;
1252 return;
1253 elsif(p_header_status = G_ENG_APPR_REJECTED) then
1254 x_attachment_status := G_REJECTED;
1255 return;
1256 end if;
1257 end if;
1258 END Get_Attachment_Status;
1259
1260 Procedure Complete_Attachment_Approval
1261 (
1262 p_api_version IN NUMBER --
1263 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1264 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1265 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1266 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1267 ,p_output_dir IN VARCHAR2 := NULL --
1268 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Complete_Attachment_Approval.log'
1269 ,x_return_status OUT NOCOPY VARCHAR2 --
1270 ,x_msg_count OUT NOCOPY NUMBER --
1271 ,x_msg_data OUT NOCOPY VARCHAR2 --
1272 ,p_change_id IN NUMBER -- header's change_id
1273 ,p_approval_status IN VARCHAR2 -- approval status
1274 )
1275 IS
1276 l_attachment_id number;
1277 l_fnd_user_id number;
1278 l_fnd_login_id number;
1279 cursor C IS
1280 select attachment_id
1281 from eng_attachment_changes
1282 where change_id = p_change_id;
1283 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Attachment_Approval';
1284 l_api_version CONSTANT NUMBER := 1.0;
1285
1286 l_return_status VARCHAR2(1);
1287 l_msg_count NUMBER;
1288 l_msg_data VARCHAR2(2000);
1289 BEGIN
1290 -- Standard Start of API savepoint
1291 SAVEPOINT Complete_Attachment_Approval;
1292 -- Standard call to check for call compatibility
1293 IF NOT FND_API.Compatible_API_Call ( l_api_version
1294 ,p_api_version
1295 ,l_api_name
1296 ,G_PKG_NAME )
1297 THEN
1298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1299 END IF;
1300 -- Initialize message list if p_init_msg_list is set to TRUE.
1301 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1302 FND_MSG_PUB.initialize;
1303 END IF ;
1304 -- For Test/Debug
1305 IF FND_API.to_Boolean( p_debug ) THEN
1306 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1307 END IF ;
1308
1309 -- Write debug message if debug mode is on
1310 IF g_debug_flag THEN
1311 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Complete_Attachment_Approval log');
1312 Write_Debug('-----------------------------------------------------');
1313 Write_Debug('p_change_id : ' || p_change_id );
1314 Write_Debug('p_approval_status : ' || p_approval_status );
1315 Write_Debug('-----------------------------------------------------');
1316 Write_Debug('Initializing return status... ' );
1317 END IF ;
1318 -- Initialize API return status to success
1319 x_return_status := FND_API.G_RET_STS_SUCCESS;
1320
1321
1322 -- Real code starts here -----------------------------------
1323 -- FND_PROFILE package is not available for workflow (WF),
1324 -- therefore manually set WHO column values
1325
1326 l_fnd_user_id := -1;
1327 l_fnd_login_id := '';
1328
1329 open C;
1330 loop
1331 fetch C into l_attachment_id;
1332 EXIT WHEN C%NOTFOUND;
1333 update fnd_attached_documents
1334 set status = p_approval_status, last_update_date = sysdate,
1335 last_updated_by = l_fnd_user_id,
1336 last_update_login = l_fnd_login_id
1337 where attached_document_id = l_attachment_id;
1338 end loop;
1339 close C;
1340
1341 -- Standard ending code ------------------------------------------------
1342 FND_MSG_PUB.Count_And_Get
1343 ( p_count => x_msg_count,
1344 p_data => x_msg_data );
1345
1346 IF g_debug_flag THEN
1347 Write_Debug('Finish. End Of Proc') ;
1348 Close_Debug_Session ;
1349 END IF ;
1350
1351 EXCEPTION
1352 WHEN FND_API.G_EXC_ERROR THEN
1353 ROLLBACK TO Complete_Attachment_Approval;
1354 x_return_status := FND_API.G_RET_STS_ERROR;
1355 FND_MSG_PUB.Count_And_Get
1356 ( p_count => x_msg_count
1357 ,p_data => x_msg_data );
1358 IF g_debug_flag THEN
1359 Write_Debug('Rollback and Finish with expected error.') ;
1360 Close_Debug_Session ;
1361 END IF ;
1362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1363 ROLLBACK TO Complete_Attachment_Approval;
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1365 FND_MSG_PUB.Count_And_Get
1366 ( p_count => x_msg_count
1367 ,p_data => x_msg_data );
1368 IF g_debug_flag THEN
1369 Write_Debug('Rollback and Finish with unexpected error.') ;
1370 Close_Debug_Session ;
1371 END IF ;
1372 WHEN OTHERS THEN
1373 ROLLBACK TO Complete_Attachment_Approval;
1374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1375 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1376 THEN
1377 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1378 END IF;
1379 FND_MSG_PUB.Count_And_Get
1380 ( p_count => x_msg_count
1381 ,p_data => x_msg_data );
1382 IF g_debug_flag THEN
1383 Write_Debug('Rollback and Finish with other error.') ;
1384 Close_Debug_Session ;
1385 END IF ;
1386
1387 END Complete_Attachment_Approval;
1388
1389 Procedure Copy_Attachments_And_Changes (
1390 p_api_version IN NUMBER --
1391 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1392 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1393 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1394 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1395 ,p_output_dir IN VARCHAR2 := NULL --
1396 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Copy_Attachments_And_Changes.log'
1397 ,x_return_status OUT NOCOPY VARCHAR2 --
1398 ,x_msg_count OUT NOCOPY NUMBER --
1399 ,x_msg_data OUT NOCOPY VARCHAR2
1400 ,p_change_id IN NUMBER -- header's change_id
1401 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
1402 ,p_org_id IN VARCHAR2
1403 ,p_inv_item_id IN VARCHAR2
1404 ,p_curr_rev_id IN VARCHAR2
1405 ,p_new_rev_id IN VARCHAR2
1406 )
1407 IS
1408
1409 cursor C IS
1410 select attached_document_id, status
1411 from fnd_attached_documents
1412 where entity_name='MTL_ITEM_REVISIONS'
1413 and pk1_value = p_org_id
1414 and pk2_value = p_inv_item_id
1415 and pk3_value = p_curr_rev_id;
1416
1417
1418 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachments_And_Changes';
1419 l_api_version CONSTANT NUMBER := 1.0;
1420 l_return_status VARCHAR2(1);
1421 l_msg_count NUMBER;
1422 l_msg_data VARCHAR2(2000);
1423 l_attachment_id NUMBER;
1424 l_new_attachment_id NUMBER;
1425 l_status VARCHAR(30);
1426 l_count NUMBER;
1427 BEGIN
1428 -- Standard Start of API savepoint
1429 SAVEPOINT Copy_Attachments_And_Changes;
1430 -- Standard call to check for call compatibility
1431 IF NOT FND_API.Compatible_API_Call ( l_api_version
1432 ,p_api_version
1433 ,l_api_name
1434 ,G_PKG_NAME )
1435 THEN
1436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1437 END IF;
1438 -- Initialize message list if p_init_msg_list is set to TRUE.
1439 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1440 FND_MSG_PUB.initialize;
1441 END IF ;
1442 -- For Test/Debug
1443 IF FND_API.to_Boolean( p_debug ) THEN
1444 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1445 END IF ;
1446
1447 -- Write debug message if debug mode is on
1448 IF g_debug_flag THEN
1449 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.COpy_Attachments_And_Changes log');
1450 Write_Debug('-----------------------------------------------------');
1451 Write_Debug('p_change_id : ' || p_change_id );
1452 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
1453 Write_Debug('p_org_id : ' || p_org_id );
1454 Write_Debug('p_inv_item_id : ' || p_inv_item_id );
1455 Write_Debug('p_curr_rev_id : ' || p_curr_rev_id );
1456 Write_Debug('p_new_rev_id : ' || p_new_rev_id );
1457 Write_Debug('-----------------------------------------------------');
1458 Write_Debug('Initializing return status... ' );
1459 END IF ;
1460 -- Initialize API return status to success
1461 x_return_status := FND_API.G_RET_STS_SUCCESS;
1462
1463 -- Real code starts here -----------------------------------------------
1464
1465 -- Copying attachment
1466 -- Step 1: If attachment exists for new revision (possible?), return
1467
1468 select count(*)
1469 into l_count
1470 from fnd_attached_documents
1471 where entity_name='MTL_ITEM_REVISIONS'
1472 and pk1_value = p_org_id
1473 and pk2_value = p_inv_item_id
1474 and pk3_value = p_new_rev_id;
1475
1476 if(l_count>0)
1477 then
1478 return;
1479 end if;
1480
1481 -- Step 2: Iterate through revision level attachments for the existing revision
1482 open C;
1483 loop
1484 fetch C into l_attachment_id, l_status;
1485 EXIT WHEN C%NOTFOUND;
1486
1487 if(l_status is NULL or l_status <> 'PENDING_CHANGE')
1488 then
1489 -- Step 3: Copying all attachments other than the pending ones
1490 DOM_ATTACHMENT_UTIL_PKG.COPY_ATTACHMENTS(x_from_entity_name => 'MTL_ITEM_REVISIONS',
1491 x_from_pk1_value => p_org_id,
1492 x_from_pk2_value => p_inv_item_id,
1493 x_from_pk3_value => p_curr_rev_id,
1494 x_from_pk4_value => '',
1495 x_from_pk5_value => '',
1496 X_from_attachment_id => l_attachment_id,
1497 x_to_entity_name => 'MTL_ITEM_REVISIONS',
1498 x_to_pk1_value => p_org_id,
1499 x_to_pk2_value => p_inv_item_id,
1500 x_to_pk3_value => p_new_rev_id,
1501 x_to_pk4_value => '',
1502 x_to_pk5_value => '',
1503 X_to_attachment_id => l_new_attachment_id,
1504 x_created_by => fnd_global.user_id,
1505 x_last_update_login => fnd_global.login_id,
1506 x_program_application_id => '',
1507 x_program_id => fnd_global.conc_program_id,
1508 x_request_id => fnd_global.conc_request_id
1509 );
1510 -- Step 4: Moving attachment changes (only Detach) from source revision to new revision
1511 update eng_attachment_changes
1512 set attachment_id = l_new_attachment_id
1513 where change_id = p_change_id
1514 and revised_item_sequence_id = p_rev_item_seq_id
1515 and attachment_id = l_attachment_id
1516 and action_type = 'DETACH';
1517
1518 else
1519 update fnd_attached_documents
1520 set pk3_value = p_new_rev_id
1521 where attached_document_id = l_attachment_id
1522 and exists
1523 (select change_document_id
1524 from eng_attachment_changes
1525 where change_id = p_change_id
1526 and revised_item_sequence_id = p_rev_item_seq_id
1527 and attachment_id = l_attachment_id);
1528 end if;
1529 end loop;
1530 close C;
1531
1532 -- Standard check of p_commit.
1533 IF FND_API.To_Boolean( p_commit ) THEN
1534 IF g_debug_flag THEN
1535 Write_Debug('Do Commit.') ;
1536 END IF ;
1537 COMMIT WORK;
1538 END IF;
1539 -- Standard ending code ------------------------------------------------
1540 FND_MSG_PUB.Count_And_Get
1541 ( p_count => x_msg_count,
1542 p_data => x_msg_data );
1543
1544 IF g_debug_flag THEN
1545 Write_Debug('Finish. End Of Proc') ;
1546 Close_Debug_Session ;
1547 END IF ;
1548
1549 EXCEPTION
1550 WHEN FND_API.G_EXC_ERROR THEN
1551 ROLLBACK TO Copy_Attachments_And_Changes;
1552 x_return_status := FND_API.G_RET_STS_ERROR;
1553 FND_MSG_PUB.Count_And_Get
1554 ( p_count => x_msg_count
1555 ,p_data => x_msg_data );
1556 IF g_debug_flag THEN
1557 Write_Debug('Rollback and Finish with expected error.') ;
1558 Close_Debug_Session ;
1559 END IF ;
1560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1561 ROLLBACK TO Copy_Attachments_And_Changes;
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563 FND_MSG_PUB.Count_And_Get
1564 ( p_count => x_msg_count
1565 ,p_data => x_msg_data );
1566 IF g_debug_flag THEN
1567 Write_Debug('Rollback and Finish with unexpected error.') ;
1568 Close_Debug_Session ;
1569 END IF ;
1570 WHEN OTHERS THEN
1571 ROLLBACK TO Copy_Attachments_And_Changes;
1572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1573 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1574 THEN
1575 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1576 END IF;
1577 FND_MSG_PUB.Count_And_Get
1578 ( p_count => x_msg_count
1579 ,p_data => x_msg_data );
1580 IF g_debug_flag THEN
1581 Write_Debug('Rollback and Finish with other error.') ;
1582 Close_Debug_Session ;
1583 END IF ;
1584 END Copy_Attachments_And_Changes;
1585
1586 Procedure Delete_Attachments_And_Changes (
1587 p_api_version IN NUMBER --
1588 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1589 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1590 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1591 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1592 ,p_output_dir IN VARCHAR2 := NULL --
1593 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments_And_Changes.log'
1594 ,x_return_status OUT NOCOPY VARCHAR2 --
1595 ,x_msg_count OUT NOCOPY NUMBER --
1596 ,x_msg_data OUT NOCOPY VARCHAR2
1597 ,p_change_id IN NUMBER -- header's change_id
1598 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
1599 ,p_org_id IN VARCHAR2
1600 ,p_inv_item_id IN VARCHAR2
1601 ,p_revision_id IN VARCHAR2
1602 )
1603 IS
1604 cursor C IS
1605 select attached_document_id, document_id
1606 from fnd_attached_documents
1607 where pk1_value = p_org_id
1608 and pk2_value = p_inv_item_id
1609 -- and (
1610 -- pk3_value = p_revision_id
1611 -- or
1612 -- (pk3_value is null and p_revision_id is null)
1613 -- ) -- commenting this out so that item level changes also get deleted when rev item is removed
1614 -- this is ok since attached_document_id is only pk anyway
1615 and attached_document_id in
1616 (select attachment_id
1617 from eng_attachment_changes
1618 where change_id = p_change_id
1619 and revised_item_sequence_id = p_rev_item_seq_id
1620 and action_type = 'ATTACH'); -- fix for 3771466
1621 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments_And_Changes';
1622 l_api_version CONSTANT NUMBER := 1.0;
1623 l_return_status VARCHAR2(1);
1624 l_msg_count NUMBER;
1625 l_msg_data VARCHAR2(2000);
1626 l_attachment_id NUMBER;
1627 l_document_id NUMBER;
1628 l_datatype_id NUMBER;
1629
1630 BEGIN
1631 -- Standard Start of API savepoint
1632 SAVEPOINT Delete_Attachments_And_Changes;
1633 -- Standard call to check for call compatibility
1634 IF NOT FND_API.Compatible_API_Call ( l_api_version
1635 ,p_api_version
1636 ,l_api_name
1637 ,G_PKG_NAME )
1638 THEN
1639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640 END IF;
1641 -- Initialize message list if p_init_msg_list is set to TRUE.
1642 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1643 FND_MSG_PUB.initialize;
1644 END IF ;
1645 -- For Test/Debug
1646 IF FND_API.to_Boolean( p_debug ) THEN
1647 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1648 END IF ;
1649
1650 -- Write debug message if debug mode is on
1651 IF g_debug_flag THEN
1652 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachments_And_Changes log');
1653 Write_Debug('-----------------------------------------------------');
1654 Write_Debug('p_change_id : ' || p_change_id );
1655 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
1656 Write_Debug('p_org_id : ' || p_org_id );
1657 Write_Debug('p_inv_item_id : ' || p_inv_item_id );
1658 Write_Debug('p_revision_id : ' || p_revision_id );
1659 Write_Debug('-----------------------------------------------------');
1660 Write_Debug('Initializing return status... ' );
1661 END IF ;
1662 -- Initialize API return status to success
1663 x_return_status := FND_API.G_RET_STS_SUCCESS;
1664
1665 -- Real code starts here -----------------------------------------------
1666 open C;
1667 loop
1668 fetch C into l_attachment_id, l_document_id;
1669 EXIT WHEN C%NOTFOUND;
1670 select datatype_id into l_datatype_id from fnd_documents where document_id = l_document_id;
1671 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
1672 end loop;
1673 close C;
1674 delete from eng_attachment_changes
1675 where change_id = p_change_id
1676 and revised_item_sequence_id = p_rev_item_seq_id;
1677 -- Standard ending code ------------------------------------------------
1678 FND_MSG_PUB.Count_And_Get
1679 ( p_count => x_msg_count,
1680 p_data => x_msg_data );
1681
1682 IF g_debug_flag THEN
1683 Write_Debug('Finish. End Of Proc') ;
1684 Close_Debug_Session ;
1685 END IF ;
1686
1687 EXCEPTION
1688 WHEN FND_API.G_EXC_ERROR THEN
1689 ROLLBACK TO Delete_Attachments_And_Changes;
1690 x_return_status := FND_API.G_RET_STS_ERROR;
1691 FND_MSG_PUB.Count_And_Get
1692 ( p_count => x_msg_count
1693 ,p_data => x_msg_data );
1694 IF g_debug_flag THEN
1695 Write_Debug('Rollback and Finish with expected error.') ;
1696 Close_Debug_Session ;
1697 END IF ;
1698 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1699 ROLLBACK TO Delete_Attachments_And_Changes;
1700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701 FND_MSG_PUB.Count_And_Get
1702 ( p_count => x_msg_count
1703 ,p_data => x_msg_data );
1704 IF g_debug_flag THEN
1705 Write_Debug('Rollback and Finish with unexpected error.') ;
1706 Close_Debug_Session ;
1707 END IF ;
1708 WHEN OTHERS THEN
1709 ROLLBACK TO Delete_Attachments_And_Changes;
1710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1711 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1712 THEN
1713 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1714 END IF;
1715 FND_MSG_PUB.Count_And_Get
1716 ( p_count => x_msg_count
1717 ,p_data => x_msg_data );
1718 IF g_debug_flag THEN
1719 Write_Debug('Rollback and Finish with other error.') ;
1720 Close_Debug_Session ;
1721 END IF ;
1722 END Delete_Attachments_And_Changes;
1723
1724 Procedure Delete_Attachments_For_Curr_CO (
1725 p_api_version IN NUMBER --
1726 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1727 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1728 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1729 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1730 ,p_output_dir IN VARCHAR2 := NULL --
1731 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments_For_Curr_CO.log'
1732 ,x_return_status OUT NOCOPY VARCHAR2 --
1733 ,x_msg_count OUT NOCOPY NUMBER --
1734 ,x_msg_data OUT NOCOPY VARCHAR2
1735 ,p_change_id IN NUMBER -- header's change_id
1736 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
1737 )
1738 IS
1739 Cursor C IS
1740 select source_document_id
1741 from eng_attachment_changes
1742 where change_id = p_change_id
1743 and revised_item_sequence_id = p_rev_item_seq_id
1744 and action_type = 'ATTACH';
1745 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments_For_Curr_CO';
1746 l_api_version CONSTANT NUMBER := 1.0;
1747 l_return_status VARCHAR2(1);
1748 l_msg_count NUMBER;
1749 l_msg_data VARCHAR2(2000);
1750 l_document_id NUMBER;
1751 l_datatype_id NUMBER;
1752 l_document_exists NUMBER;
1753 BEGIN
1754 null;
1755 /*
1756 -- Standard Start of API savepoint
1757 SAVEPOINT Delete_Attachments_For_Curr_CO;
1758 -- Standard call to check for call compatibility
1759 IF NOT FND_API.Compatible_API_Call ( l_api_version
1760 ,p_api_version
1761 ,l_api_name
1762 ,G_PKG_NAME )
1763 THEN
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 END IF;
1766 -- Initialize message list if p_init_msg_list is set to TRUE.
1767 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1768 FND_MSG_PUB.initialize;
1769 END IF ;
1770 -- For Test/Debug
1771 IF FND_API.to_Boolean( p_debug ) THEN
1772 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1773 END IF ;
1774
1775 -- Write debug message if debug mode is on
1776 IF g_debug_flag THEN
1777 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachment_For_Curr_CO log');
1778 Write_Debug('-----------------------------------------------------');
1779 Write_Debug('p_change_id : ' || p_change_id );
1780 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
1781 Write_Debug('-----------------------------------------------------');
1782 Write_Debug('Initializing return status... ' );
1783 END IF ;
1784 -- Initialize API return status to success
1785 x_return_status := FND_API.G_RET_STS_SUCCESS;
1786
1787 -- Real code starts here -----------------------------------------------
1788 open C;
1789 loop
1790 fetch C into l_document_id;
1791 EXIT WHEN C%NOTFOUND;
1792 -- select document_id into l_document_id from fnd_attached_documents
1793 -- where attached_document_id = l_attachment_id;
1794 select datatype_id into l_datatype_id
1795 from fnd_documents
1796 where document_id = l_document_id;
1797
1798 -- Temporarily deleting the attachment from fnd_attached_document
1799 -- instead of deleting from the fnd_documents table
1800 -- until the fnd_documents_pkg is fixed
1801
1802 -- Check if the document exists as part of any other change order
1803 SELECT count(*) into l_document_exists
1804 FROM eng_attachment_changes
1805 WHERE source_document_id = l_document_id
1806 AND change_id <> p_change_id
1807 AND revised_item_sequence_id <> p_rev_item_seq_id;
1808
1809 IF l_document_exists > 0 THEN
1810 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'N');
1811 END IF;
1812 end loop;
1813 close C;
1814
1815 -- Standard ending code ------------------------------------------------
1816 FND_MSG_PUB.Count_And_Get
1817 ( p_count => x_msg_count,
1818 p_data => x_msg_data );
1819
1820 IF g_debug_flag THEN
1821 Write_Debug('Finish. End Of Proc') ;
1822 Close_Debug_Session ;
1823 END IF ;
1824
1825 EXCEPTION
1826 WHEN FND_API.G_EXC_ERROR THEN
1827 ROLLBACK TO Delete_Attachments_For_Curr_CO;
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 FND_MSG_PUB.Count_And_Get
1830 ( p_count => x_msg_count
1831 ,p_data => x_msg_data );
1832 IF g_debug_flag THEN
1833 Write_Debug('Rollback and Finish with expected error.') ;
1834 Close_Debug_Session ;
1835 END IF ;
1836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1837 ROLLBACK TO Delete_Attachments_For_Curr_CO;
1838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1839 FND_MSG_PUB.Count_And_Get
1840 ( p_count => x_msg_count
1841 ,p_data => x_msg_data );
1842 IF g_debug_flag THEN
1843 Write_Debug('Rollback and Finish with unexpected error.') ;
1844 Close_Debug_Session ;
1845 END IF ;
1846 WHEN OTHERS THEN
1847 ROLLBACK TO Delete_Attachments_For_Curr_CO;
1848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1849 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1850 THEN
1851 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1852 END IF;
1853 FND_MSG_PUB.Count_And_Get
1854 ( p_count => x_msg_count
1855 ,p_data => x_msg_data );
1856 IF g_debug_flag THEN
1857 Write_Debug('Rollback and Finish with other error.') ;
1858 Close_Debug_Session ;
1859 END IF ;
1860 */
1861 END Delete_Attachments_For_Curr_CO;
1862
1863 Procedure Delete_Attachments (
1864 p_api_version IN NUMBER --
1865 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1866 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1867 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1868 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1869 ,p_output_dir IN VARCHAR2 := NULL --
1870 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments.log'
1871 ,x_return_status OUT NOCOPY VARCHAR2 --
1872 ,x_msg_count OUT NOCOPY NUMBER --
1873 ,x_msg_data OUT NOCOPY VARCHAR2
1874 ,p_org_id IN VARCHAR2
1875 ,p_inv_item_id IN VARCHAR2
1876 ,p_revision_id IN VARCHAR2
1877 )
1878 IS
1879 cursor C IS
1880 select attached_document_id, document_id
1881 from fnd_attached_documents
1882 where pk1_value = p_org_id
1883 and pk2_value = p_inv_item_id
1884 and pk3_value = p_revision_id;
1885 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments';
1886 l_api_version CONSTANT NUMBER := 1.0;
1887 l_return_status VARCHAR2(1);
1888 l_msg_count NUMBER;
1889 l_msg_data VARCHAR2(2000);
1890 l_attachment_id NUMBER;
1891 l_document_id NUMBER;
1892 l_datatype_id NUMBER;
1893 BEGIN
1894 -- Standard Start of API savepoint
1895 SAVEPOINT Delete_Attachments;
1896 -- Standard call to check for call compatibility
1897 IF NOT FND_API.Compatible_API_Call ( l_api_version
1898 ,p_api_version
1899 ,l_api_name
1900 ,G_PKG_NAME )
1901 THEN
1902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1903 END IF;
1904 -- Initialize message list if p_init_msg_list is set to TRUE.
1905 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1906 FND_MSG_PUB.initialize;
1907 END IF ;
1908 -- For Test/Debug
1909 IF FND_API.to_Boolean( p_debug ) THEN
1910 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1911 END IF ;
1912
1913 -- Write debug message if debug mode is on
1914 IF g_debug_flag THEN
1915 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachments log');
1916 Write_Debug('-----------------------------------------------------');
1917 Write_Debug('p_org_id : ' || p_org_id );
1918 Write_Debug('p_inv_item_id : ' || p_inv_item_id );
1919 Write_Debug('p_revision_id : ' || p_revision_id );
1920 Write_Debug('-----------------------------------------------------');
1921 Write_Debug('Initializing return status... ' );
1922 END IF ;
1923 -- Initialize API return status to success
1924 x_return_status := FND_API.G_RET_STS_SUCCESS;
1925
1926 -- Real code starts here -----------------------------------------------
1927 open C;
1928 loop
1929 fetch C into l_attachment_id, l_document_id;
1930 EXIT WHEN C%NOTFOUND;
1931 select datatype_id into l_datatype_id from fnd_documents where document_id = l_document_id;
1932 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
1933 end loop;
1934 close C;
1935 -- Standard ending code ------------------------------------------------
1936 FND_MSG_PUB.Count_And_Get
1937 ( p_count => x_msg_count,
1938 p_data => x_msg_data );
1939
1940 IF g_debug_flag THEN
1941 Write_Debug('Finish. End Of Proc') ;
1942 Close_Debug_Session ;
1943 END IF ;
1944
1945 EXCEPTION
1946 WHEN FND_API.G_EXC_ERROR THEN
1947 ROLLBACK TO Delete_Attachments;
1948 x_return_status := FND_API.G_RET_STS_ERROR;
1949 FND_MSG_PUB.Count_And_Get
1950 ( p_count => x_msg_count
1951 ,p_data => x_msg_data );
1952 IF g_debug_flag THEN
1953 Write_Debug('Rollback and Finish with expected error.') ;
1954 Close_Debug_Session ;
1955 END IF ;
1956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1957 ROLLBACK TO Delete_Attachments;
1958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959 FND_MSG_PUB.Count_And_Get
1960 ( p_count => x_msg_count
1961 ,p_data => x_msg_data );
1962 IF g_debug_flag THEN
1963 Write_Debug('Rollback and Finish with unexpected error.') ;
1964 Close_Debug_Session ;
1965 END IF ;
1966 WHEN OTHERS THEN
1967 ROLLBACK TO Delete_Attachments;
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1969 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1970 THEN
1971 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1972 END IF;
1973 FND_MSG_PUB.Count_And_Get
1974 ( p_count => x_msg_count
1975 ,p_data => x_msg_data );
1976 IF g_debug_flag THEN
1977 Write_Debug('Rollback and Finish with other error.') ;
1978 Close_Debug_Session ;
1979 END IF ;
1980 END Delete_Attachments;
1981
1982 Procedure Delete_Attachment (
1983 p_api_version IN NUMBER --
1984 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1985 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1986 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1987 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1988 ,p_output_dir IN VARCHAR2 := NULL --
1989 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachment.log'
1990 ,x_return_status OUT NOCOPY VARCHAR2 --
1991 ,x_msg_count OUT NOCOPY NUMBER --
1992 ,x_msg_data OUT NOCOPY VARCHAR2
1993 ,p_attachment_id IN NUMBER
1994 )
1995 IS
1996 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachment';
1997 l_api_version CONSTANT NUMBER := 1.0;
1998 l_return_status VARCHAR2(1);
1999 l_msg_count NUMBER;
2000 l_msg_data VARCHAR2(2000);
2001 l_document_id NUMBER;
2002 l_datatype_id NUMBER;
2003 BEGIN
2004 -- Standard Start of API savepoint
2005 SAVEPOINT Delete_Attachment;
2006 -- Standard call to check for call compatibility
2007 IF NOT FND_API.Compatible_API_Call ( l_api_version
2008 ,p_api_version
2009 ,l_api_name
2010 ,G_PKG_NAME )
2011 THEN
2012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2013 END IF;
2014 -- Initialize message list if p_init_msg_list is set to TRUE.
2015 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2016 FND_MSG_PUB.initialize;
2017 END IF ;
2018 -- For Test/Debug
2019 IF FND_API.to_Boolean( p_debug ) THEN
2020 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
2021 END IF ;
2022
2023 -- Write debug message if debug mode is on
2024 IF g_debug_flag THEN
2025 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachment log');
2026 Write_Debug('-----------------------------------------------------');
2027 Write_Debug('p_attachment_id : ' || p_attachment_id );
2028 Write_Debug('-----------------------------------------------------');
2029 Write_Debug('Initializing return status... ' );
2030 END IF ;
2031 -- Initialize API return status to success
2032 x_return_status := FND_API.G_RET_STS_SUCCESS;
2033
2034 -- Real code starts here -----------------------------------------------
2035 begin
2036 select document_id into l_document_id
2037 from fnd_attached_documents
2038 where attached_document_id = p_attachment_id;
2039 select datatype_id into l_datatype_id
2040 from fnd_documents
2041 where document_id = l_document_id;
2042 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
2043 exception
2044 when NO_DATA_FOUND then
2045 return;
2046 end;
2047 -- Standard ending code ------------------------------------------------
2048 FND_MSG_PUB.Count_And_Get
2049 ( p_count => x_msg_count,
2050 p_data => x_msg_data );
2051
2052 IF g_debug_flag THEN
2053 Write_Debug('Finish. End Of Proc') ;
2054 Close_Debug_Session ;
2055 END IF ;
2056
2057 EXCEPTION
2058 WHEN FND_API.G_EXC_ERROR THEN
2059 ROLLBACK TO Delete_Attachment;
2060 x_return_status := FND_API.G_RET_STS_ERROR;
2061 FND_MSG_PUB.Count_And_Get
2062 ( p_count => x_msg_count
2063 ,p_data => x_msg_data );
2064 IF g_debug_flag THEN
2065 Write_Debug('Rollback and Finish with expected error.') ;
2066 Close_Debug_Session ;
2067 END IF ;
2068 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2069 ROLLBACK TO Delete_Attachment;
2070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2071 FND_MSG_PUB.Count_And_Get
2072 ( p_count => x_msg_count
2073 ,p_data => x_msg_data );
2074 IF g_debug_flag THEN
2075 Write_Debug('Rollback and Finish with unexpected error.') ;
2076 Close_Debug_Session ;
2077 END IF ;
2078 WHEN OTHERS THEN
2079 ROLLBACK TO Delete_Attachment;
2080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2081 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2082 THEN
2083 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2084 END IF;
2085 FND_MSG_PUB.Count_And_Get
2086 ( p_count => x_msg_count
2087 ,p_data => x_msg_data );
2088 IF g_debug_flag THEN
2089 Write_Debug('Rollback and Finish with other error.') ;
2090 Close_Debug_Session ;
2091 END IF ;
2092 END Delete_Attachment;
2093
2094 Procedure Delete_Changes (
2095 p_api_version IN NUMBER --
2096 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
2097 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
2098 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2099 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
2100 ,p_output_dir IN VARCHAR2 := NULL --
2101 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Changes.log'
2102 ,x_return_status OUT NOCOPY VARCHAR2 --
2103 ,x_msg_count OUT NOCOPY NUMBER --
2104 ,x_msg_data OUT NOCOPY VARCHAR2
2105 ,p_change_id IN NUMBER -- header's change_id
2106 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
2107 )
2108 IS
2109 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Changes';
2110 l_api_version CONSTANT NUMBER := 1.0;
2111 l_return_status VARCHAR2(1);
2112 l_msg_count NUMBER;
2113 l_msg_data VARCHAR2(2000);
2114 BEGIN
2115 -- Standard Start of API savepoint
2116 SAVEPOINT Delete_Changes;
2117 -- Standard call to check for call compatibility
2118 IF NOT FND_API.Compatible_API_Call ( l_api_version
2119 ,p_api_version
2120 ,l_api_name
2121 ,G_PKG_NAME )
2122 THEN
2123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2124 END IF;
2125 -- Initialize message list if p_init_msg_list is set to TRUE.
2126 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2127 FND_MSG_PUB.initialize;
2128 END IF ;
2129 -- For Test/Debug
2130 IF FND_API.to_Boolean( p_debug ) THEN
2131 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
2132 END IF ;
2133
2134 -- Write debug message if debug mode is on
2135 IF g_debug_flag THEN
2136 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Changes log');
2137 Write_Debug('-----------------------------------------------------');
2138 Write_Debug('p_change_id : ' || p_change_id );
2139 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
2140 Write_Debug('-----------------------------------------------------');
2141 Write_Debug('Initializing return status... ' );
2142 END IF ;
2143 -- Initialize API return status to success
2144 x_return_status := FND_API.G_RET_STS_SUCCESS;
2145
2146 -- Real code starts here -----------------------------------------------
2147 delete from eng_attachment_changes
2148 where change_id = p_change_id
2149 and revised_item_sequence_id = p_rev_item_seq_id;
2150 -- Standard ending code ------------------------------------------------
2151 FND_MSG_PUB.Count_And_Get
2152 ( p_count => x_msg_count,
2153 p_data => x_msg_data );
2154
2155 IF g_debug_flag THEN
2156 Write_Debug('Finish. End Of Proc') ;
2157 Close_Debug_Session ;
2158 END IF ;
2159
2160 EXCEPTION
2161 WHEN FND_API.G_EXC_ERROR THEN
2162 ROLLBACK TO Delete_Changes;
2163 x_return_status := FND_API.G_RET_STS_ERROR;
2164 FND_MSG_PUB.Count_And_Get
2165 ( p_count => x_msg_count
2166 ,p_data => x_msg_data );
2167 IF g_debug_flag THEN
2168 Write_Debug('Rollback and Finish with expected error.') ;
2169 Close_Debug_Session ;
2170 END IF ;
2171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2172 ROLLBACK TO Delete_Changes;
2173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2174 FND_MSG_PUB.Count_And_Get
2175 ( p_count => x_msg_count
2176 ,p_data => x_msg_data );
2177 IF g_debug_flag THEN
2178 Write_Debug('Rollback and Finish with unexpected error.') ;
2179 Close_Debug_Session ;
2180 END IF ;
2181 WHEN OTHERS THEN
2182 ROLLBACK TO Delete_Changes;
2183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2184 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2185 THEN
2186 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2187 END IF;
2188 FND_MSG_PUB.Count_And_Get
2189 ( p_count => x_msg_count
2190 ,p_data => x_msg_data );
2191 IF g_debug_flag THEN
2192 Write_Debug('Rollback and Finish with other error.') ;
2193 Close_Debug_Session ;
2194 END IF ;
2195 END Delete_Changes;
2196
2197 Procedure Validate_floating_version (
2198 p_api_version IN NUMBER --
2199 ,x_return_status OUT NOCOPY VARCHAR2 --
2200 ,x_msg_count OUT NOCOPY NUMBER --
2201 ,x_msg_data OUT NOCOPY VARCHAR2
2202 ,p_change_id IN NUMBER
2203 ,p_rev_item_seq_id IN NUMBER := NULL
2204 )
2205 IS
2206 Cursor get_floating_attachments(l_change_id NUMBER,
2207 l_revised_item_seq_id NUMBER) is
2208 select change_id, revised_item_sequence_id,
2209 category_id, file_name, source_path,
2210 pk1_value, pk2_value, pk3_value
2211 from eng_attachment_changes a
2212 where a.change_id = l_change_id
2213 and datatype_id = 8 -- only webservices files
2214 and family_id = 0 -- for floating version files
2215 and action_type in ('ATTACH','CHANGE_REVISION','CHANGE_VERSION_LABEL')
2216 and revised_item_sequence_id in (select decode(l_revised_item_seq_id,null, (select revised_item_sequence_id
2217 from eng_revised_items
2218 where change_id = a.change_id),
2219 l_revised_item_seq_id) from dual);
2220
2221 l_change_id NUMBER;
2222 l_revised_item_seq_id NUMBER;
2223 l_change_policy VARCHAR2(100);
2224 l_api_name VARCHAR2(100) := 'Validate_floating_version';
2225
2226 change_policy_defined EXCEPTION;
2227
2228
2229 BEGIN
2230
2231 SAVEPOINT Implement_Attachment_Change;
2232
2233 -- Initialize API return status to success
2234 x_return_status := FND_API.G_RET_STS_SUCCESS;
2235
2236 l_change_id := p_change_id;
2237 l_revised_item_seq_id := p_rev_item_seq_id;
2238
2239 FOR c2 in get_floating_attachments(l_change_id, l_revised_item_seq_id)
2240 LOOP
2241
2242 BEGIN
2243
2244 -- The foll. SQL checks if the category passed has changepolicy defined
2245 -- on it or not
2246 SELECT ecp.policy_char_value INTO l_change_policy
2247 FROM
2248 (select nvl(mirb.lifecycle_id, msi.lifecycle_id) as lifecycle_id,
2249 nvl(mirb.current_phase_id , msi.current_phase_id) as phase_id,
2250 msi.item_catalog_group_id item_catalog_group_id,
2251 msi.inventory_item_id, msi.organization_id , mirb.revision_id
2252 from mtl_item_revisions_b mirb,
2253 MTL_SYSTEM_ITEMS msi
2254 where mirb.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
2255 and mirb.ORGANIZATION_ID(+)= msi.ORGANIZATION_ID
2256 and mirb.revision_id(+) = c2.pk3_value
2257 and msi.INVENTORY_ITEM_ID = c2.pk2_value
2258 and msi.ORGANIZATION_ID = c2.pk1_value) ITEM_DTLS,
2259 ENG_CHANGE_POLICIES_V ECP
2260 WHERE
2261 ecp.policy_object_pk1_value =
2262 (SELECT TO_CHAR(ic.item_catalog_group_id)
2263 FROM mtl_item_catalog_groups_b ic
2264 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
2265 FROM EGO_OBJ_TYPE_LIFECYCLES olc
2266 WHERE olc.object_id = (SELECT OBJECT_ID
2267 FROM fnd_objects
2268 WHERE obj_name = 'EGO_ITEM')
2269 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
2270 AND olc.object_classification_code = ic.item_catalog_group_id
2271 )
2272 AND ROWNUM = 1
2273 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2274 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
2275 AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
2276 AND ecp.policy_object_pk3_value = ITEM_DTLS.phase_id
2277 and ecp.policy_object_name = 'CATALOG_LIFECYCLE_PHASE'
2278 and ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
2279 and ecp.attribute_code = 'ATTACHMENT'
2280 and attribute_number_value = c2.category_id;
2281
2282 IF l_change_policy = 'CHANGE_ORDER_REQUIRED' THEN
2283 RAISE change_policy_defined;
2284 ROLLBACK TO Implement_Attachment_Change;
2285 END IF;
2286
2287 EXCEPTION
2288 WHEN NO_DATA_FOUND THEN -- no data found means there are no change
2289 -- policy defined for the category
2290 null;
2291 END;
2292
2293 END LOOP;
2294
2295 EXCEPTION
2296 WHEN change_policy_defined THEN
2297 x_return_status := FND_API.G_RET_STS_ERROR;
2298 x_msg_count := 1;
2299 x_msg_data := 'Error: This Change Order has floating version attachments that are under change required change policy. Such CO cannot be implemented';
2300 RAISE FND_API.G_EXC_ERROR;
2301 WHEN OTHERS THEN
2302 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2303 THEN
2304 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2305 END IF;
2306 FND_MSG_PUB.Count_And_Get
2307 ( p_count => x_msg_count
2308 ,p_data => x_msg_data );
2309
2310 END Validate_floating_version;
2311
2312
2313 END ENG_ATTACHMENT_IMPLEMENTATION;