[Home] [Help]
PACKAGE BODY: APPS.ENG_ATTACHMENT_IMPLEMENTATION
Source
1 package body ENG_ATTACHMENT_IMPLEMENTATION as
2 /*$Header: ENGUATTB.pls 120.22.12010000.2 2010/02/03 08:02:06 maychen ship $ */
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 ------------------------------
1587 /*************Added for bug 8329527 ****************/
1588 Procedure Migrate_Attachment_And_Change (
1589 p_api_version IN NUMBER --
1590 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1591 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1592 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1593 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1594 ,p_output_dir IN VARCHAR2 := NULL --
1595 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Migrate_Attachment_And_Change.log'
1596 ,x_return_status OUT NOCOPY VARCHAR2 --
1597 ,x_msg_count OUT NOCOPY NUMBER --
1598 ,x_msg_data OUT NOCOPY VARCHAR2
1599 ,p_change_id IN NUMBER -- header's change_id
1600 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
1601 ,p_org_id IN VARCHAR2
1602 ,p_inv_item_id IN VARCHAR2
1603 ,p_curr_rev_id IN VARCHAR2
1604 ,p_new_rev_id IN VARCHAR2
1605 )
1606 IS
1607 cursor C IS
1608 select attached_document_id, status
1609 from fnd_attached_documents
1610 where pk1_value = p_org_id
1611 and pk2_value = p_inv_item_id
1612 and pk3_value = p_curr_rev_id;
1613
1614 l_api_name CONSTANT VARCHAR2(30) := 'Migrate_Attachment_And_Change';
1615 l_api_version CONSTANT NUMBER := 1.0;
1616 l_return_status VARCHAR2(1);
1617 l_msg_count NUMBER;
1618 l_msg_data VARCHAR2(2000);
1619 l_attachment_id NUMBER;
1620 l_new_attachment_id NUMBER;
1621 l_status VARCHAR(30);
1622 l_count NUMBER;
1623 BEGIN
1624 -- Standard Start of API savepoint
1625 SAVEPOINT Migrate_Attachment_And_Change;
1626 -- Standard call to check for call compatibility
1627 IF NOT FND_API.Compatible_API_Call ( l_api_version
1628 ,p_api_version
1629 ,l_api_name
1630 ,G_PKG_NAME )
1631 THEN
1632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1633 END IF;
1634 -- Initialize message list if p_init_msg_list is set to TRUE.
1635 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1636 FND_MSG_PUB.initialize;
1637 END IF ;
1638 -- For Test/Debug
1639 IF FND_API.to_Boolean( p_debug ) THEN
1640 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1641 END IF ;
1642
1643 -- Write debug message if debug mode is on
1644 IF g_debug_flag THEN
1645 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Migrate_Attachment_And_Change log');
1646 Write_Debug('-----------------------------------------------------');
1647 Write_Debug('p_change_id : ' || p_change_id );
1648 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
1649 Write_Debug('p_org_id : ' || p_org_id );
1650 Write_Debug('p_inv_item_id : ' || p_inv_item_id );
1651 Write_Debug('p_curr_rev_id : ' || p_curr_rev_id );
1652 Write_Debug('p_new_rev_id : ' || p_new_rev_id );
1653 Write_Debug('-----------------------------------------------------');
1654 Write_Debug('Initializing return status... ' );
1655 END IF ;
1656 -- Initialize API return status to success
1657 x_return_status := FND_API.G_RET_STS_SUCCESS;
1658
1659 -- Real code starts here -----------------------------------------------
1660 select count(*)
1661 into l_count
1662 from fnd_attached_documents
1663 where pk1_value = p_org_id
1664 and pk2_value = p_inv_item_id
1665 and pk3_value = p_new_rev_id;
1666 if(l_count>0) then
1667 return;
1668 end if;
1669 open C;
1670 loop
1671 fetch C into l_attachment_id, l_status;
1672 EXIT WHEN C%NOTFOUND;
1673 if(l_status is NULL or l_status <> 'PENDING_CHANGE') then
1674 /*Copy implemented attachments from 'FROM Revision' to 'New Revision'
1675 * migrate DETACH attachment change lines to NEW REVISION
1676 */
1677 DOM_ATTACHMENT_UTIL_PKG.COPY_ATTACHMENTS(x_from_entity_name => 'MTL_ITEM_REVISIONS',
1678 x_from_pk1_value => p_org_id,
1679 x_from_pk2_value => p_inv_item_id,
1680 x_from_pk3_value => p_curr_rev_id,
1681 x_from_pk4_value => '',
1682 x_from_pk5_value => '',
1683 X_from_attachment_id => l_attachment_id,
1684 x_to_entity_name => 'MTL_ITEM_REVISIONS',
1685 x_to_pk1_value => p_org_id,
1686 x_to_pk2_value => p_inv_item_id,
1687 x_to_pk3_value => p_new_rev_id,
1688 x_to_pk4_value => '',
1689 x_to_pk5_value => '',
1690 X_to_attachment_id => l_new_attachment_id,
1691 x_created_by => fnd_global.user_id,
1692 x_last_update_login => fnd_global.login_id,
1693 x_program_application_id => '',
1694 x_program_id => fnd_global.conc_program_id,
1695 x_request_id => fnd_global.conc_request_id
1696 );
1697
1698 update eng_attachment_changes
1699 set attachment_id = l_new_attachment_id,
1700 source_document_id = (select document_id
1701 from fnd_attached_documents
1702 where attached_document_id =
1703 l_new_attachment_id),
1704 pk3_value = p_new_rev_id
1705 where change_id = p_change_id
1706 and revised_item_sequence_id = p_rev_item_seq_id
1707 and attachment_id = l_attachment_id
1708 and action_type = 'DETACH';
1709 if(l_status is not null) then
1710 update fnd_attached_documents
1711 set status = null
1712 where attached_document_id = l_new_attachment_id;
1713 end if;
1714 else
1715 update fnd_attached_documents
1716 set pk3_value = p_new_rev_id
1717 where attached_document_id = l_attachment_id
1718 and exists
1719 (select change_document_id
1720 from eng_attachment_changes
1721 where change_id = p_change_id
1722 and revised_item_sequence_id = p_rev_item_seq_id
1723 and attachment_id = l_attachment_id);
1724 end if;
1725 end loop;
1726 close C;
1727
1728 /* Update pk3_value(rev_id) from 'From Revision' to 'New Revision' for 'ATTACH' change lines */
1729
1730 update eng_attachment_changes set pk3_value = p_new_rev_id
1731 where change_id = p_change_id
1732 and revised_item_sequence_id = p_rev_item_seq_id
1733 and action_type = 'ATTACH'
1734 and pk3_value = p_curr_rev_id ;
1735
1736
1737 -- Standard check of p_commit.
1738 IF FND_API.To_Boolean( p_commit ) THEN
1739 IF g_debug_flag THEN
1740 Write_Debug('Do Commit.') ;
1741 END IF ;
1742 COMMIT WORK;
1743 END IF;
1744 -- Standard ending code ------------------------------------------------
1745 FND_MSG_PUB.Count_And_Get
1746 ( p_count => x_msg_count,
1747 p_data => x_msg_data );
1748
1749 IF g_debug_flag THEN
1750 Write_Debug('Finish. End Of Proc') ;
1751 Close_Debug_Session ;
1752 END IF ;
1753
1754 EXCEPTION
1755 WHEN FND_API.G_EXC_ERROR THEN
1756 ROLLBACK TO Migrate_Attachment_And_Change;
1757 x_return_status := FND_API.G_RET_STS_ERROR;
1758 FND_MSG_PUB.Count_And_Get
1759 ( p_count => x_msg_count
1760 ,p_data => x_msg_data );
1761 IF g_debug_flag THEN
1762 Write_Debug('Rollback and Finish with expected error.') ;
1763 Close_Debug_Session ;
1764 END IF ;
1765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1766 ROLLBACK TO Migrate_Attachment_And_Change;
1767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1768 FND_MSG_PUB.Count_And_Get
1769 ( p_count => x_msg_count
1770 ,p_data => x_msg_data );
1771 IF g_debug_flag THEN
1772 Write_Debug('Rollback and Finish with unexpected error.') ;
1773 Close_Debug_Session ;
1774 END IF ;
1775 WHEN OTHERS THEN
1776 ROLLBACK TO Migrate_Attachment_And_Change;
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1778 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1779 THEN
1780 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1781 END IF;
1782 FND_MSG_PUB.Count_And_Get
1783 ( p_count => x_msg_count
1784 ,p_data => x_msg_data );
1785 IF g_debug_flag THEN
1786 Write_Debug('Rollback and Finish with other error.') ;
1787 Close_Debug_Session ;
1788 END IF ;
1789 END Migrate_Attachment_And_Change;
1790
1791
1792 ------------------------------
1793 Procedure Delete_Attachments_And_Changes (
1794 p_api_version IN NUMBER --
1795 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1796 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1797 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1798 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1799 ,p_output_dir IN VARCHAR2 := NULL --
1800 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments_And_Changes.log'
1801 ,x_return_status OUT NOCOPY VARCHAR2 --
1802 ,x_msg_count OUT NOCOPY NUMBER --
1803 ,x_msg_data OUT NOCOPY VARCHAR2
1804 ,p_change_id IN NUMBER -- header's change_id
1805 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
1806 ,p_org_id IN VARCHAR2
1807 ,p_inv_item_id IN VARCHAR2
1808 ,p_revision_id IN VARCHAR2
1809 )
1810 IS
1811 cursor C IS
1812 select attached_document_id, document_id
1813 from fnd_attached_documents
1814 where pk1_value = p_org_id
1815 and pk2_value = p_inv_item_id
1816 -- and (
1817 -- pk3_value = p_revision_id
1818 -- or
1819 -- (pk3_value is null and p_revision_id is null)
1820 -- ) -- commenting this out so that item level changes also get deleted when rev item is removed
1821 -- this is ok since attached_document_id is only pk anyway
1822 and attached_document_id in
1823 (select attachment_id
1824 from eng_attachment_changes
1825 where change_id = p_change_id
1826 and revised_item_sequence_id = p_rev_item_seq_id
1827 and action_type = 'ATTACH'); -- fix for 3771466
1828 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments_And_Changes';
1829 l_api_version CONSTANT NUMBER := 1.0;
1830 l_return_status VARCHAR2(1);
1831 l_msg_count NUMBER;
1832 l_msg_data VARCHAR2(2000);
1833 l_attachment_id NUMBER;
1834 l_document_id NUMBER;
1835 l_datatype_id NUMBER;
1836
1837 BEGIN
1838 -- Standard Start of API savepoint
1839 SAVEPOINT Delete_Attachments_And_Changes;
1840 -- Standard call to check for call compatibility
1841 IF NOT FND_API.Compatible_API_Call ( l_api_version
1842 ,p_api_version
1843 ,l_api_name
1844 ,G_PKG_NAME )
1845 THEN
1846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1847 END IF;
1848 -- Initialize message list if p_init_msg_list is set to TRUE.
1849 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1850 FND_MSG_PUB.initialize;
1851 END IF ;
1852 -- For Test/Debug
1853 IF FND_API.to_Boolean( p_debug ) THEN
1854 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1855 END IF ;
1856
1857 -- Write debug message if debug mode is on
1858 IF g_debug_flag THEN
1859 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachments_And_Changes log');
1860 Write_Debug('-----------------------------------------------------');
1861 Write_Debug('p_change_id : ' || p_change_id );
1862 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
1863 Write_Debug('p_org_id : ' || p_org_id );
1864 Write_Debug('p_inv_item_id : ' || p_inv_item_id );
1865 Write_Debug('p_revision_id : ' || p_revision_id );
1866 Write_Debug('-----------------------------------------------------');
1867 Write_Debug('Initializing return status... ' );
1868 END IF ;
1869 -- Initialize API return status to success
1870 x_return_status := FND_API.G_RET_STS_SUCCESS;
1871
1872 -- Real code starts here -----------------------------------------------
1873 open C;
1874 loop
1875 fetch C into l_attachment_id, l_document_id;
1876 EXIT WHEN C%NOTFOUND;
1877 select datatype_id into l_datatype_id from fnd_documents where document_id = l_document_id;
1878 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
1879 end loop;
1880 close C;
1881 delete from eng_attachment_changes
1882 where change_id = p_change_id
1883 and revised_item_sequence_id = p_rev_item_seq_id;
1884 -- Standard ending code ------------------------------------------------
1885 FND_MSG_PUB.Count_And_Get
1886 ( p_count => x_msg_count,
1887 p_data => x_msg_data );
1888
1889 IF g_debug_flag THEN
1890 Write_Debug('Finish. End Of Proc') ;
1891 Close_Debug_Session ;
1892 END IF ;
1893
1894 EXCEPTION
1895 WHEN FND_API.G_EXC_ERROR THEN
1896 ROLLBACK TO Delete_Attachments_And_Changes;
1897 x_return_status := FND_API.G_RET_STS_ERROR;
1898 FND_MSG_PUB.Count_And_Get
1899 ( p_count => x_msg_count
1900 ,p_data => x_msg_data );
1901 IF g_debug_flag THEN
1902 Write_Debug('Rollback and Finish with expected error.') ;
1903 Close_Debug_Session ;
1904 END IF ;
1905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1906 ROLLBACK TO Delete_Attachments_And_Changes;
1907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1908 FND_MSG_PUB.Count_And_Get
1909 ( p_count => x_msg_count
1910 ,p_data => x_msg_data );
1911 IF g_debug_flag THEN
1912 Write_Debug('Rollback and Finish with unexpected error.') ;
1913 Close_Debug_Session ;
1914 END IF ;
1915 WHEN OTHERS THEN
1916 ROLLBACK TO Delete_Attachments_And_Changes;
1917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1918 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1919 THEN
1920 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
1921 END IF;
1922 FND_MSG_PUB.Count_And_Get
1923 ( p_count => x_msg_count
1924 ,p_data => x_msg_data );
1925 IF g_debug_flag THEN
1926 Write_Debug('Rollback and Finish with other error.') ;
1927 Close_Debug_Session ;
1928 END IF ;
1929 END Delete_Attachments_And_Changes;
1930
1931 Procedure Delete_Attachments_For_Curr_CO (
1932 p_api_version IN NUMBER --
1933 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
1934 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
1935 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1936 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
1937 ,p_output_dir IN VARCHAR2 := NULL --
1938 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments_For_Curr_CO.log'
1939 ,x_return_status OUT NOCOPY VARCHAR2 --
1940 ,x_msg_count OUT NOCOPY NUMBER --
1941 ,x_msg_data OUT NOCOPY VARCHAR2
1942 ,p_change_id IN NUMBER -- header's change_id
1943 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
1944 )
1945 IS
1946 Cursor C IS
1947 select source_document_id
1948 from eng_attachment_changes
1949 where change_id = p_change_id
1950 and revised_item_sequence_id = p_rev_item_seq_id
1951 and action_type = 'ATTACH';
1952 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments_For_Curr_CO';
1953 l_api_version CONSTANT NUMBER := 1.0;
1954 l_return_status VARCHAR2(1);
1955 l_msg_count NUMBER;
1956 l_msg_data VARCHAR2(2000);
1957 l_document_id NUMBER;
1958 l_datatype_id NUMBER;
1959 l_document_exists NUMBER;
1960 BEGIN
1961 null;
1962 /*
1963 -- Standard Start of API savepoint
1964 SAVEPOINT Delete_Attachments_For_Curr_CO;
1965 -- Standard call to check for call compatibility
1966 IF NOT FND_API.Compatible_API_Call ( l_api_version
1967 ,p_api_version
1968 ,l_api_name
1969 ,G_PKG_NAME )
1970 THEN
1971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1972 END IF;
1973 -- Initialize message list if p_init_msg_list is set to TRUE.
1974 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1975 FND_MSG_PUB.initialize;
1976 END IF ;
1977 -- For Test/Debug
1978 IF FND_API.to_Boolean( p_debug ) THEN
1979 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1980 END IF ;
1981
1982 -- Write debug message if debug mode is on
1983 IF g_debug_flag THEN
1984 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachment_For_Curr_CO log');
1985 Write_Debug('-----------------------------------------------------');
1986 Write_Debug('p_change_id : ' || p_change_id );
1987 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
1988 Write_Debug('-----------------------------------------------------');
1989 Write_Debug('Initializing return status... ' );
1990 END IF ;
1991 -- Initialize API return status to success
1992 x_return_status := FND_API.G_RET_STS_SUCCESS;
1993
1994 -- Real code starts here -----------------------------------------------
1995 open C;
1996 loop
1997 fetch C into l_document_id;
1998 EXIT WHEN C%NOTFOUND;
1999 -- select document_id into l_document_id from fnd_attached_documents
2000 -- where attached_document_id = l_attachment_id;
2001 select datatype_id into l_datatype_id
2002 from fnd_documents
2003 where document_id = l_document_id;
2004
2005 -- Temporarily deleting the attachment from fnd_attached_document
2006 -- instead of deleting from the fnd_documents table
2007 -- until the fnd_documents_pkg is fixed
2008
2009 -- Check if the document exists as part of any other change order
2010 SELECT count(*) into l_document_exists
2011 FROM eng_attachment_changes
2012 WHERE source_document_id = l_document_id
2013 AND change_id <> p_change_id
2014 AND revised_item_sequence_id <> p_rev_item_seq_id;
2015
2016 IF l_document_exists > 0 THEN
2017 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'N');
2018 END IF;
2019 end loop;
2020 close C;
2021
2022 -- Standard ending code ------------------------------------------------
2023 FND_MSG_PUB.Count_And_Get
2024 ( p_count => x_msg_count,
2025 p_data => x_msg_data );
2026
2027 IF g_debug_flag THEN
2028 Write_Debug('Finish. End Of Proc') ;
2029 Close_Debug_Session ;
2030 END IF ;
2031
2032 EXCEPTION
2033 WHEN FND_API.G_EXC_ERROR THEN
2034 ROLLBACK TO Delete_Attachments_For_Curr_CO;
2035 x_return_status := FND_API.G_RET_STS_ERROR;
2036 FND_MSG_PUB.Count_And_Get
2037 ( p_count => x_msg_count
2038 ,p_data => x_msg_data );
2039 IF g_debug_flag THEN
2040 Write_Debug('Rollback and Finish with expected error.') ;
2041 Close_Debug_Session ;
2042 END IF ;
2043 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2044 ROLLBACK TO Delete_Attachments_For_Curr_CO;
2045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2046 FND_MSG_PUB.Count_And_Get
2047 ( p_count => x_msg_count
2048 ,p_data => x_msg_data );
2049 IF g_debug_flag THEN
2050 Write_Debug('Rollback and Finish with unexpected error.') ;
2051 Close_Debug_Session ;
2052 END IF ;
2053 WHEN OTHERS THEN
2054 ROLLBACK TO Delete_Attachments_For_Curr_CO;
2055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2056 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2057 THEN
2058 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2059 END IF;
2060 FND_MSG_PUB.Count_And_Get
2061 ( p_count => x_msg_count
2062 ,p_data => x_msg_data );
2063 IF g_debug_flag THEN
2064 Write_Debug('Rollback and Finish with other error.') ;
2065 Close_Debug_Session ;
2066 END IF ;
2067 */
2068 END Delete_Attachments_For_Curr_CO;
2069
2070 Procedure Delete_Attachments (
2071 p_api_version IN NUMBER --
2072 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
2073 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
2074 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2075 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
2076 ,p_output_dir IN VARCHAR2 := NULL --
2077 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachments.log'
2078 ,x_return_status OUT NOCOPY VARCHAR2 --
2079 ,x_msg_count OUT NOCOPY NUMBER --
2080 ,x_msg_data OUT NOCOPY VARCHAR2
2081 ,p_org_id IN VARCHAR2
2082 ,p_inv_item_id IN VARCHAR2
2083 ,p_revision_id IN VARCHAR2
2084 )
2085 IS
2086 cursor C IS
2087 select attached_document_id, document_id
2088 from fnd_attached_documents
2089 where pk1_value = p_org_id
2090 and pk2_value = p_inv_item_id
2091 and pk3_value = p_revision_id;
2092 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments';
2093 l_api_version CONSTANT NUMBER := 1.0;
2094 l_return_status VARCHAR2(1);
2095 l_msg_count NUMBER;
2096 l_msg_data VARCHAR2(2000);
2097 l_attachment_id NUMBER;
2098 l_document_id NUMBER;
2099 l_datatype_id NUMBER;
2100 BEGIN
2101 -- Standard Start of API savepoint
2102 SAVEPOINT Delete_Attachments;
2103 -- Standard call to check for call compatibility
2104 IF NOT FND_API.Compatible_API_Call ( l_api_version
2105 ,p_api_version
2106 ,l_api_name
2107 ,G_PKG_NAME )
2108 THEN
2109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2110 END IF;
2111 -- Initialize message list if p_init_msg_list is set to TRUE.
2112 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2113 FND_MSG_PUB.initialize;
2114 END IF ;
2115 -- For Test/Debug
2116 IF FND_API.to_Boolean( p_debug ) THEN
2117 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
2118 END IF ;
2119
2120 -- Write debug message if debug mode is on
2121 IF g_debug_flag THEN
2122 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachments log');
2123 Write_Debug('-----------------------------------------------------');
2124 Write_Debug('p_org_id : ' || p_org_id );
2125 Write_Debug('p_inv_item_id : ' || p_inv_item_id );
2126 Write_Debug('p_revision_id : ' || p_revision_id );
2127 Write_Debug('-----------------------------------------------------');
2128 Write_Debug('Initializing return status... ' );
2129 END IF ;
2130 -- Initialize API return status to success
2131 x_return_status := FND_API.G_RET_STS_SUCCESS;
2132
2133 -- Real code starts here -----------------------------------------------
2134 open C;
2135 loop
2136 fetch C into l_attachment_id, l_document_id;
2137 EXIT WHEN C%NOTFOUND;
2138 select datatype_id into l_datatype_id from fnd_documents where document_id = l_document_id;
2139 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
2140 end loop;
2141 close C;
2142 -- Standard ending code ------------------------------------------------
2143 FND_MSG_PUB.Count_And_Get
2144 ( p_count => x_msg_count,
2145 p_data => x_msg_data );
2146
2147 IF g_debug_flag THEN
2148 Write_Debug('Finish. End Of Proc') ;
2149 Close_Debug_Session ;
2150 END IF ;
2151
2152 EXCEPTION
2153 WHEN FND_API.G_EXC_ERROR THEN
2154 ROLLBACK TO Delete_Attachments;
2155 x_return_status := FND_API.G_RET_STS_ERROR;
2156 FND_MSG_PUB.Count_And_Get
2157 ( p_count => x_msg_count
2158 ,p_data => x_msg_data );
2159 IF g_debug_flag THEN
2160 Write_Debug('Rollback and Finish with expected error.') ;
2161 Close_Debug_Session ;
2162 END IF ;
2163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2164 ROLLBACK TO Delete_Attachments;
2165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2166 FND_MSG_PUB.Count_And_Get
2167 ( p_count => x_msg_count
2168 ,p_data => x_msg_data );
2169 IF g_debug_flag THEN
2170 Write_Debug('Rollback and Finish with unexpected error.') ;
2171 Close_Debug_Session ;
2172 END IF ;
2173 WHEN OTHERS THEN
2174 ROLLBACK TO Delete_Attachments;
2175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2176 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2177 THEN
2178 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2179 END IF;
2180 FND_MSG_PUB.Count_And_Get
2181 ( p_count => x_msg_count
2182 ,p_data => x_msg_data );
2183 IF g_debug_flag THEN
2184 Write_Debug('Rollback and Finish with other error.') ;
2185 Close_Debug_Session ;
2186 END IF ;
2187 END Delete_Attachments;
2188
2189 Procedure Delete_Attachment (
2190 p_api_version IN NUMBER --
2191 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
2192 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
2193 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2194 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
2195 ,p_output_dir IN VARCHAR2 := NULL --
2196 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Attachment.log'
2197 ,x_return_status OUT NOCOPY VARCHAR2 --
2198 ,x_msg_count OUT NOCOPY NUMBER --
2199 ,x_msg_data OUT NOCOPY VARCHAR2
2200 ,p_attachment_id IN NUMBER
2201 )
2202 IS
2203 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachment';
2204 l_api_version CONSTANT NUMBER := 1.0;
2205 l_return_status VARCHAR2(1);
2206 l_msg_count NUMBER;
2207 l_msg_data VARCHAR2(2000);
2208 l_document_id NUMBER;
2209 l_datatype_id NUMBER;
2210 BEGIN
2211 -- Standard Start of API savepoint
2212 SAVEPOINT Delete_Attachment;
2213 -- Standard call to check for call compatibility
2214 IF NOT FND_API.Compatible_API_Call ( l_api_version
2215 ,p_api_version
2216 ,l_api_name
2217 ,G_PKG_NAME )
2218 THEN
2219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2220 END IF;
2221 -- Initialize message list if p_init_msg_list is set to TRUE.
2222 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2223 FND_MSG_PUB.initialize;
2224 END IF ;
2225 -- For Test/Debug
2226 IF FND_API.to_Boolean( p_debug ) THEN
2227 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
2228 END IF ;
2229
2230 -- Write debug message if debug mode is on
2231 IF g_debug_flag THEN
2232 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Attachment log');
2233 Write_Debug('-----------------------------------------------------');
2234 Write_Debug('p_attachment_id : ' || p_attachment_id );
2235 Write_Debug('-----------------------------------------------------');
2236 Write_Debug('Initializing return status... ' );
2237 END IF ;
2238 -- Initialize API return status to success
2239 x_return_status := FND_API.G_RET_STS_SUCCESS;
2240
2241 -- Real code starts here -----------------------------------------------
2242 begin
2243 select document_id into l_document_id
2244 from fnd_attached_documents
2245 where attached_document_id = p_attachment_id;
2246 select datatype_id into l_datatype_id
2247 from fnd_documents
2248 where document_id = l_document_id;
2249 fnd_documents_pkg.delete_row(l_document_id, l_datatype_id, 'Y');
2250 exception
2251 when NO_DATA_FOUND then
2252 return;
2253 end;
2254 -- Standard ending code ------------------------------------------------
2255 FND_MSG_PUB.Count_And_Get
2256 ( p_count => x_msg_count,
2257 p_data => x_msg_data );
2258
2259 IF g_debug_flag THEN
2260 Write_Debug('Finish. End Of Proc') ;
2261 Close_Debug_Session ;
2262 END IF ;
2263
2264 EXCEPTION
2265 WHEN FND_API.G_EXC_ERROR THEN
2266 ROLLBACK TO Delete_Attachment;
2267 x_return_status := FND_API.G_RET_STS_ERROR;
2268 FND_MSG_PUB.Count_And_Get
2269 ( p_count => x_msg_count
2270 ,p_data => x_msg_data );
2271 IF g_debug_flag THEN
2272 Write_Debug('Rollback and Finish with expected error.') ;
2273 Close_Debug_Session ;
2274 END IF ;
2275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2276 ROLLBACK TO Delete_Attachment;
2277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2278 FND_MSG_PUB.Count_And_Get
2279 ( p_count => x_msg_count
2280 ,p_data => x_msg_data );
2281 IF g_debug_flag THEN
2282 Write_Debug('Rollback and Finish with unexpected error.') ;
2283 Close_Debug_Session ;
2284 END IF ;
2285 WHEN OTHERS THEN
2286 ROLLBACK TO Delete_Attachment;
2287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2288 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2289 THEN
2290 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2291 END IF;
2292 FND_MSG_PUB.Count_And_Get
2293 ( p_count => x_msg_count
2294 ,p_data => x_msg_data );
2295 IF g_debug_flag THEN
2296 Write_Debug('Rollback and Finish with other error.') ;
2297 Close_Debug_Session ;
2298 END IF ;
2299 END Delete_Attachment;
2300
2301 Procedure Delete_Changes (
2302 p_api_version IN NUMBER --
2303 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
2304 ,p_commit IN VARCHAR2 := FND_API.G_FALSE --
2305 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2306 ,p_debug IN VARCHAR2 := FND_API.G_FALSE --
2307 ,p_output_dir IN VARCHAR2 := NULL --
2308 ,p_debug_filename IN VARCHAR2 := 'ENGUATTB.Delete_Changes.log'
2309 ,x_return_status OUT NOCOPY VARCHAR2 --
2310 ,x_msg_count OUT NOCOPY NUMBER --
2311 ,x_msg_data OUT NOCOPY VARCHAR2
2312 ,p_change_id IN NUMBER -- header's change_id
2313 ,p_rev_item_seq_id IN NUMBER -- revised item sequence id
2314 )
2315 IS
2316 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Changes';
2317 l_api_version CONSTANT NUMBER := 1.0;
2318 l_return_status VARCHAR2(1);
2319 l_msg_count NUMBER;
2320 l_msg_data VARCHAR2(2000);
2321 BEGIN
2322 -- Standard Start of API savepoint
2323 SAVEPOINT Delete_Changes;
2324 -- Standard call to check for call compatibility
2325 IF NOT FND_API.Compatible_API_Call ( l_api_version
2326 ,p_api_version
2327 ,l_api_name
2328 ,G_PKG_NAME )
2329 THEN
2330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2331 END IF;
2332 -- Initialize message list if p_init_msg_list is set to TRUE.
2333 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2334 FND_MSG_PUB.initialize;
2335 END IF ;
2336 -- For Test/Debug
2337 IF FND_API.to_Boolean( p_debug ) THEN
2338 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
2339 END IF ;
2340
2341 -- Write debug message if debug mode is on
2342 IF g_debug_flag THEN
2343 Write_Debug('ENG_ATTACHMENT_IMPLEMENTATION.Delete_Changes log');
2344 Write_Debug('-----------------------------------------------------');
2345 Write_Debug('p_change_id : ' || p_change_id );
2346 Write_Debug('p_rev_item_seq_id : ' || p_rev_item_seq_id );
2347 Write_Debug('-----------------------------------------------------');
2348 Write_Debug('Initializing return status... ' );
2349 END IF ;
2350 -- Initialize API return status to success
2351 x_return_status := FND_API.G_RET_STS_SUCCESS;
2352
2353 -- Real code starts here -----------------------------------------------
2354 delete from eng_attachment_changes
2355 where change_id = p_change_id
2356 and revised_item_sequence_id = p_rev_item_seq_id;
2357 -- Standard ending code ------------------------------------------------
2358 FND_MSG_PUB.Count_And_Get
2359 ( p_count => x_msg_count,
2360 p_data => x_msg_data );
2361
2362 IF g_debug_flag THEN
2363 Write_Debug('Finish. End Of Proc') ;
2364 Close_Debug_Session ;
2365 END IF ;
2366
2367 EXCEPTION
2368 WHEN FND_API.G_EXC_ERROR THEN
2369 ROLLBACK TO Delete_Changes;
2370 x_return_status := FND_API.G_RET_STS_ERROR;
2371 FND_MSG_PUB.Count_And_Get
2372 ( p_count => x_msg_count
2373 ,p_data => x_msg_data );
2374 IF g_debug_flag THEN
2375 Write_Debug('Rollback and Finish with expected error.') ;
2376 Close_Debug_Session ;
2377 END IF ;
2378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2379 ROLLBACK TO Delete_Changes;
2380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2381 FND_MSG_PUB.Count_And_Get
2382 ( p_count => x_msg_count
2383 ,p_data => x_msg_data );
2384 IF g_debug_flag THEN
2385 Write_Debug('Rollback and Finish with unexpected error.') ;
2386 Close_Debug_Session ;
2387 END IF ;
2388 WHEN OTHERS THEN
2389 ROLLBACK TO Delete_Changes;
2390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2391 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2392 THEN
2393 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2394 END IF;
2395 FND_MSG_PUB.Count_And_Get
2396 ( p_count => x_msg_count
2397 ,p_data => x_msg_data );
2398 IF g_debug_flag THEN
2399 Write_Debug('Rollback and Finish with other error.') ;
2400 Close_Debug_Session ;
2401 END IF ;
2402 END Delete_Changes;
2403
2404 Procedure Validate_floating_version (
2405 p_api_version IN NUMBER --
2406 ,x_return_status OUT NOCOPY VARCHAR2 --
2407 ,x_msg_count OUT NOCOPY NUMBER --
2408 ,x_msg_data OUT NOCOPY VARCHAR2
2409 ,p_change_id IN NUMBER
2410 ,p_rev_item_seq_id IN NUMBER := NULL
2411 )
2412 IS
2413 Cursor get_floating_attachments(l_change_id NUMBER,
2414 l_revised_item_seq_id NUMBER) is
2415 select change_id, revised_item_sequence_id,
2416 category_id, file_name, source_path,
2417 pk1_value, pk2_value, pk3_value
2418 from eng_attachment_changes a
2419 where a.change_id = l_change_id
2420 and datatype_id = 8 -- only webservices files
2421 and family_id = 0 -- for floating version files
2422 and action_type in ('ATTACH','CHANGE_REVISION','CHANGE_VERSION_LABEL')
2423 and revised_item_sequence_id in (select decode(l_revised_item_seq_id,null, (select revised_item_sequence_id
2424 from eng_revised_items
2425 where change_id = a.change_id),
2426 l_revised_item_seq_id) from dual);
2427
2428 l_change_id NUMBER;
2429 l_revised_item_seq_id NUMBER;
2430 l_change_policy VARCHAR2(100);
2431 l_api_name VARCHAR2(100) := 'Validate_floating_version';
2432
2433 change_policy_defined EXCEPTION;
2434
2435
2436 BEGIN
2437
2438 SAVEPOINT Implement_Attachment_Change;
2439
2440 -- Initialize API return status to success
2441 x_return_status := FND_API.G_RET_STS_SUCCESS;
2442
2443 l_change_id := p_change_id;
2444 l_revised_item_seq_id := p_rev_item_seq_id;
2445
2446 FOR c2 in get_floating_attachments(l_change_id, l_revised_item_seq_id)
2447 LOOP
2448
2449 BEGIN
2450
2451 -- The foll. SQL checks if the category passed has changepolicy defined
2452 -- on it or not
2453 SELECT ecp.policy_char_value INTO l_change_policy
2454 FROM
2455 (select nvl(mirb.lifecycle_id, msi.lifecycle_id) as lifecycle_id,
2456 nvl(mirb.current_phase_id , msi.current_phase_id) as phase_id,
2457 msi.item_catalog_group_id item_catalog_group_id,
2458 msi.inventory_item_id, msi.organization_id , mirb.revision_id
2459 from mtl_item_revisions_b mirb,
2460 MTL_SYSTEM_ITEMS msi
2461 where mirb.INVENTORY_ITEM_ID(+) = msi.INVENTORY_ITEM_ID
2462 and mirb.ORGANIZATION_ID(+)= msi.ORGANIZATION_ID
2463 and mirb.revision_id(+) = c2.pk3_value
2464 and msi.INVENTORY_ITEM_ID = c2.pk2_value
2465 and msi.ORGANIZATION_ID = c2.pk1_value) ITEM_DTLS,
2466 ENG_CHANGE_POLICIES_V ECP
2467 WHERE
2468 ecp.policy_object_pk1_value =
2469 (SELECT TO_CHAR(ic.item_catalog_group_id)
2470 FROM mtl_item_catalog_groups_b ic
2471 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
2472 FROM EGO_OBJ_TYPE_LIFECYCLES olc
2473 WHERE olc.object_id = (SELECT OBJECT_ID
2474 FROM fnd_objects
2475 WHERE obj_name = 'EGO_ITEM')
2476 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
2477 AND olc.object_classification_code = ic.item_catalog_group_id
2478 )
2479 AND ROWNUM = 1
2480 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2481 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
2482 AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
2483 AND ecp.policy_object_pk3_value = ITEM_DTLS.phase_id
2484 and ecp.policy_object_name = 'CATALOG_LIFECYCLE_PHASE'
2485 and ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
2486 and ecp.attribute_code = 'ATTACHMENT'
2487 and attribute_number_value = c2.category_id;
2488
2489 IF l_change_policy = 'CHANGE_ORDER_REQUIRED' THEN
2490 RAISE change_policy_defined;
2491 ROLLBACK TO Implement_Attachment_Change;
2492 END IF;
2493
2494 EXCEPTION
2495 WHEN NO_DATA_FOUND THEN -- no data found means there are no change
2496 -- policy defined for the category
2497 null;
2498 END;
2499
2500 END LOOP;
2501
2502 EXCEPTION
2503 WHEN change_policy_defined THEN
2504 x_return_status := FND_API.G_RET_STS_ERROR;
2505 x_msg_count := 1;
2506 x_msg_data := 'Error: This Change Order has floating version attachments that are under change required change policy. Such CO cannot be implemented';
2507 RAISE FND_API.G_EXC_ERROR;
2508 WHEN OTHERS THEN
2509 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2510 THEN
2511 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
2512 END IF;
2513 FND_MSG_PUB.Count_And_Get
2514 ( p_count => x_msg_count
2515 ,p_data => x_msg_data );
2516
2517 END Validate_floating_version;
2518
2519
2520 END ENG_ATTACHMENT_IMPLEMENTATION;