1 PACKAGE BODY PSP_ROLLBACK_EFF_REPORTS AS
2 /*$Header: PSPERRBB.pls 120.1.12010000.1 2008/07/28 08:06:20 appldev ship $*/
3
4 PROCEDURE DELETE_EFF_REPORTS(
5 errBuf OUT NOCOPY VARCHAR2,
6 retCode OUT NOCOPY VARCHAR2,
7 p_request_id IN NUMBER,
8 p_person_id IN NUMBER
9 ) IS
10
11 TYPE t_varchar2_240_type is TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
12 l_itemkey t_varchar2_240_type ;
13 l_parent_itemkey t_varchar2_240_type ;
14
15 CURSOR effort_master_csr IS
16 select effort_report_id, person_id
17 from psp_eff_reports
18 WHERE request_id = p_request_id
19 AND nvl(p_person_id, person_id) = person_id ;
20
21 CURSOR c_get_item_key (p_request_id NUMBER) is
22 select nvl(PARENT_ITEM_KEY,ITEM_KEY), ITEM_KEY from wf_items_v
23 where ITEM_TYPE = 'PSPERAVL'
24 and ITEM_KEY in (
25 select pera.WF_ITEM_KEY --into l_itemkey
26 from psp_eff_Report_approvals pera,
27 psp_eff_report_details perd,
28 psp_eff_reports per
29 where pera.EFFORT_REPORT_DETAIL_ID = perd.EFFORT_REPORT_DETAIL_ID
30 and perd.EFFORT_REPORT_ID = per.EFFORT_REPORT_ID
31 and per.request_id = p_request_id );
32
33 CURSOR c_get_approved_eff_rep(p_request_id NUMBER,P_PERSON_ID NUMBER) is
34 SELECT 1
35 FROM PSP_EFF_REPORTS per ,
36 PSP_REPORT_TEMPLATES_H PRTH
37 WHERE per.STATUS_CODE ='A'
38 AND per.request_id= p_request_id
39 and nvl(P_PERSON_ID ,per.person_id)= per.PERSON_ID
40 AND PER.REQUEST_ID = PRTH.REQUEST_ID
41 AND PRTH.APPROVAL_TYPE <>'PRE'
42 and rownum=1;
43
44 l_period_name VARCHAR2(80);
45 l_deleted BOOLEAN := TRUE;
46 l_appr_exists NUMBER;
47 cnt NUMBER := 0;
48 l_eff_report_approved Exception;
49 l_approved_eff_rep_exist Number:= 0;
50
51 BEGIN
52 -- fnd_msg_pub.initialize;
53
54 fnd_file.put_line (FND_FILE.LOG, 'p_person_id= '||p_person_id );
55
56 OPEN c_get_approved_eff_rep(p_request_id,P_PERSON_ID);
57 FETCH c_get_approved_eff_rep INTO l_approved_eff_rep_exist;
58 CLOSE c_get_approved_eff_rep;
59
60 if l_approved_eff_rep_exist = 1 then
61 -- One or more effort reports in Approved status already exists for the person
62 raise l_eff_report_approved;
63 end if;
64
65 /*
66 SELECT 1 INTO l_appr_exists FROM PSP_EFF_REPORTS per WHERE per.STATUS_CODE ='A'
67 AND per.request_id= p_request_id and nvl(P_PERSON_ID ,per.person_id)= per.PERSON_ID
68 and rownum=1;
69
70
71
72 EXCEPTION WHEN NO_DATA_FOUND THEN
73 l_appr_exists := 0;
74
75 END;
76
77 IF (l_appr_exists =1) then
78 raise l_eff_report_approved;
79 fnd_message.set_name('PSP', 'PSP_EFF_REP_APPR_STATUS');
80
81 -- One or more effort reports in Approved status already exists for the person
82
83 ELSE
84 */
85
86 /* Cancel outstanding notifications*/
87 OPEN c_get_item_key(p_request_id);
88 FETCH c_get_item_key BULK COLLECT INTO l_parent_itemkey, l_itemkey;
89 CLOSE c_get_item_key;
90 FOR i in 1..l_parent_itemkey.count
91 LOOP
92 BEGIN
93 WF_ENGINE.AbortProcess(itemtype =>'PSPERAVL',
94 itemkey => l_parent_itemkey(i) ,
95 process => null,
96 result =>'eng_force',
97 verify_lock => true,
98 cascade =>true);
99 EXCEPTION
100 WHEN others THEN
101 null;
102 END;
103 /*Delete recodrs for all the initiator thread */
104
105 DELETE fnd_lobs fl
106 WHERE EXISTS (SELECT 1
107 FROM fnd_attached_documents fad,
108 fnd_documents_vl fdl
109 WHERE fad.pk1_value = l_itemkey(i)
110 AND fdl.document_id = fad.document_id
111 AND fdl.media_id = fl.file_id
112 AND fad.entity_name = 'ERDETAILS');
113
114 /*Delete recodrs for all the Approver thread */
115 DELETE fnd_lobs fl
116 WHERE EXISTS (SELECT 1
117 FROM fnd_attached_documents fad,
118 fnd_documents_vl fdl
119 WHERE fad.pk1_value = l_itemkey(i)
120 AND fdl.document_id = fad.document_id
121 AND fdl.media_id = fl.file_id
122 AND fad.entity_name = 'ERDETAILS');
123
124 END LOOP;
125
126
127
128
129 OPEN effort_master_csr;
130
131 FETCH effort_master_csr BULK COLLECT into eff_report_master_rec.effort_report_id, eff_report_master_rec.person_id;
132
133 /*
134
135 FORALL i in 1..eff_report_master_rec.effort_report_id.count
136 select effort_report_detail_id BULK COLLECT into eff_report_details_rec.effort_report_detail_id
137 from psp_eff_report_details where
138 effort_report_id =eff_report_master_rec.effort_report_id(i) ;
139
140 Implementation Restriction forall, BULK COLLECT and SELECT do not work together
141 */
142
143
144 FORALL i in 1.. eff_report_master_rec.effort_report_id.count
145 delete from psp_eff_report_approvals where effort_report_detail_id in (
146 select effort_report_detail_id from psp_eff_report_details where effort_report_id
147 = eff_report_master_rec.effort_report_id(i));
148
149 FORALL i in 1.. eff_report_master_rec.effort_report_id.count
150 delete from psp_eff_report_details where effort_report_detail_id in (
151 select effort_report_detail_id from psp_eff_report_details where effort_report_id
152 = eff_report_master_rec.effort_report_id(i));
153
154
155 FORALL i in 1..eff_report_master_rec.effort_report_id.count
156 Delete from psp_eff_reports where effort_report_id = eff_report_master_rec.effort_report_id(i);
157
158 /*
159
160 FORALL i in 1.. eff_report_details_rec.effort_report_detail_id.count
161 delete from psp_eff_report_details where effort_report_detail_id = eff_report_details_rec.effort_report_detail_id(i);
162
163 */
164 Delete from psp_report_errors where request_id = p_request_id;
165
166 psp_message_s.print_success;
167
168 --END IF;
169 EXCEPTION
170 WHEN l_eff_report_approved THEN
171 ROLLBACK;
172 fnd_message.set_name('PSP', 'PSP_EFF_REP_APPR_STATUS');
173 fnd_msg_pub.add;
174 retCode :=2;
175
176 WHEN OTHERS
177 THEN
178 ROLLBACK;
179 fnd_message.set_name('PSP','PSP_SQL_ERROR');
180 fnd_message.set_token('SQLERROR',sqlerrm);
181 fnd_msg_pub.add;
182 psp_message_s.print_error(p_mode => FND_FILE.LOG,
183 p_print_header => FND_API.G_TRUE);
184 retCode :=2;
185
186 END delete_eff_reports;
187 END psp_rollback_eff_reports;
[Home] [Help]