[Home] [Help]
PACKAGE BODY: APPS.EDR_ADHOC_USER_PVT
Source
1 PACKAGE BODY EDR_ADHOC_USER_PVT AS
2 /* $Header: EDRVADHB.pls 120.1.12000000.1 2007/01/18 05:56:02 appldev ship $
3
4 /* Exception declaration */
5 -- defined a exception if there is from edr_psig
6 EDR_PSIG_ERROR Exception;
7
8 -- Bug 2674799 : start
9 -- Update approver list for the signer process
10 procedure UPDATE_SIGNERLIST (
11 p_event_id IN number,
12 p_event_name IN VARCHAR2 ,
13 p_document_id IN number,
14 p_originalrecipient IN FND_TABLE_OF_VARCHAR2_255,
15 p_finalrecipient IN FND_TABLE_OF_VARCHAR2_255,
16 p_overridingdetails IN FND_TABLE_OF_VARCHAR2_255,
17 p_signaturesequence IN FND_TABLE_OF_VARCHAR2_255,
18 p_recipientdisplayname IN FND_TABLE_OF_VARCHAR2_255 DEFAULT NULL,
19 p_originating_system IN FND_TABLE_OF_VARCHAR2_255 DEFAULT NULL,
20 p_orignating_system_id IN FND_TABLE_OF_VARCHAR2_255 DEFAULT NULL,
21 x_error OUT NOCOPY NUMBER,
22 x_error_msg OUT NOCOPY VARCHAR2
23 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
24
25
26 l_final_recipient varchar2(100);
27 l_original_recipient varchar2(100);
28 l_signature_sequence number;
29 l_overriding_approver varchar2(80);
30 l_overriding_comments varchar2(4000);
31 i integer;
32 l_temp_signature_id number;
33 l_psig_signature_id number;
34 l_actiondone varchar2(100);
35 l_error number;
36 l_error_msg varchar2(4000);
37 l_status varchar2(1);
38 l_adhoc_status varchar2(32);
39 l_updatestatus FND_TABLE_OF_VARCHAR2_255 := FND_TABLE_OF_VARCHAR2_255();
40 cursor GET_SIGNERS_FOR_EVENT is
41 --Bug 4272262: Start
42 select user_name, original_recipient, to_number(signature_sequence,'999999999999.999999')
43 --Bug 4272262: End
44 from edr_esignatures
45 where event_id = p_event_id
46 --Bug 4272262: Start
47 order by to_number(signature_sequence,'999999999999.999999');
48 --Bug 4272262: Start
49 BEGIN
50 --create the updatestatus array of the required length and
51 --update the status as Action reqd for the row
52 for i in 1 .. p_originalrecipient.count loop
53 l_updatestatus.extend;
54 l_updatestatus(i):= G_ACTION_REQD;
55 end loop;
56 -- open the cursor
57 -- this is done for updating or deleting the default signers,
58 -- insert of adhoc users is done after the loop of current rows
59 open GET_SIGNERS_FOR_EVENT;
60 loop
61 fetch GET_SIGNERS_FOR_EVENT into l_final_recipient,l_original_recipient,
62 l_signature_sequence;
63 EXIT WHEN GET_SIGNERS_FOR_EVENT%NOTFOUND;
64
65 l_psig_signature_id := null;
66 l_actiondone := G_ACTION_REQD;
67 l_error := null;
68 l_error_msg := null;
69
70 -- get the signature id from edr_psig
71 edr_psig.getSignatureId(
72 P_DOCUMENT_ID => P_DOCUMENT_ID,
73 P_ORIGINAL_RECIPIENT=> l_original_recipient,
74 P_USER_NAME => l_final_recipient,
75 P_SIGNATURE_STATUS => 'PENDING',
76 X_SIGNATURE_ID => l_psig_signature_id,
77 X_ERROR => l_error,
78 X_ERROR_MSG => l_error_msg);
79 IF (nvl(l_error,0) >0) then
80 RAISE EDR_PSIG_ERROR;
81 END IF;
82
83 for i in 1 .. p_originalrecipient.count loop
84 if p_originalrecipient(i) is not null then
85 --match the final recipient and original recipient
86 if ( p_originalrecipient(i) = l_original_recipient and
87 p_finalrecipient(i) = l_final_recipient) then
88 -- check for signer sequence
89 --if (i <> l_signature_sequence) then
90 -- update the signature sequence in the
91 -- temp and GQ tables
92
93 --Bug 2674799: start
94 -- Use the signature sequence passed to this API.
95
96 UPDATE EDR_ESIGNATURES
97 SET SIGNATURE_SEQUENCE = p_signaturesequence(i)
98 WHERE EVENT_ID = p_event_id
99 AND USER_NAME =l_final_recipient
100 AND ORIGINAL_RECIPIENT = l_original_recipient;
101
102 edr_psig.update_signature_sequence (
103 P_SIGNATURE_ID => l_psig_signature_id,
104 P_SIGNATURE_SEQUENCE =>
105 p_signaturesequence(i),
106 X_ERROR => l_error,
107 X_ERROR_MSG => l_error_msg);
108 --Bug 2674799 : end
109
110 IF (nvl(l_error,0) >0) then
111 RAISE EDR_PSIG_ERROR;
112 end if;
113 --end if;
114 l_updatestatus(i) := G_NO_ACTION_REQD;
115 l_actiondone := G_NO_ACTION_REQD;
116 end if;
117 end if;
118 end loop;
119
120 -- if the original recipeint and final recipeint is not found in the row
121 -- set, delete the row
122 if (l_actiondone = G_ACTION_REQD) then
123 DELETE FROM EDR_ESIGNATURES
124 WHERE EVENT_ID = p_event_id
125 AND USER_NAME = l_final_recipient
126 AND ORIGINAL_RECIPIENT = l_original_recipient;
127
128 -- get the adhoc status for the signatureid
129 -- if the adhoc status is ADDED then delete
130 -- delete the user else cancel the signature
131
132 edr_psig.get_adhoc_status (P_SIGNATURE_ID => l_psig_signature_id,
133 X_STATUS => l_adhoc_status,
134 X_ERROR => l_error,
135 X_ERROR_MSG => l_error_msg);
136
137 IF (nvl(l_error,0) >0) then
138 RAISE EDR_PSIG_ERROR;
139 END IF;
140
141 IF (l_adhoc_status = 'ADDED') then
142 edr_psig.delete_adhoc_user (P_SIGNATURE_ID => l_psig_signature_id,
143 X_ERROR => l_error,
144 X_ERROR_MSG => l_error_msg);
145 IF (nvl(l_error,0) >0) then
146 RAISE EDR_PSIG_ERROR;
147 END IF;
148
149 else
150 edr_psig.cancelSignature ( P_SIGNATURE_ID => l_psig_signature_id,
151 P_ERROR => l_error,
152 P_ERROR_MSG => l_error_msg);
153 IF (nvl(l_error,0) >0) then
154 RAISE EDR_PSIG_ERROR;
155 END IF;
156
157 edr_psig.update_adhoc_status ( P_SIGNATURE_ID =>
158 l_psig_signature_id,
159 P_ADHOC_STATUS => 'DELETED',
160 X_ERROR => l_error,
161 X_ERROR_MSG => l_error_msg);
162 IF (nvl(l_error,0) >0) then
163 RAISE EDR_PSIG_ERROR;
164 END IF;
165 end if;
166 end if;-- end l_action_done
167 end loop;
168 CLOSE GET_SIGNERS_FOR_EVENT;
169
170 for i in 1 .. p_originalrecipient.count loop
171 if p_originalrecipient(i) is NOT NULL then
172 l_status := l_updatestatus(i);
173 l_original_recipient := p_originalrecipient(i);
174 l_error := null;
175 l_error_msg := null;
176 l_overriding_approver := null;
177 l_overriding_comments := null;
178
179 if ((l_status is null) or (l_status = G_ACTION_REQD)) then
180 -- you already have the final recipient and overriding details
181 l_overriding_approver := p_finalrecipient(i);
182 l_overriding_comments := p_overridingdetails(i);
183 SELECT EDR_ESIGNATURES_S.NEXTVAL into l_temp_signature_id from
184 DUAL;
185 --Bug 2674799 : start
186
187 INSERT into EDR_ESIGNATURES
188 (
189 SIGNATURE_ID,
190 EVENT_ID,
191 EVENT_NAME,
192 USER_NAME,
193 SIGNATURE_SEQUENCE,
194 SIGNATURE_STATUS,
195 ADHOC_STATUS,
196 ORIGINAL_RECIPIENT,
197 SIGNATURE_OVERRIDING_COMMENTS
198 )
199 values
200 (
201 l_temp_signature_id,
202 p_event_id,
203 p_event_name,
204 l_overriding_approver,
205 p_signaturesequence(i),
206 'PENDING',
207 'ADDED',
208 l_original_recipient,
209 l_overriding_comments
210 );
211
212 EDR_PSIG.REQUESTSIGNATURE(P_DOCUMENT_ID => p_document_id ,
213 P_USER_NAME => l_overriding_approver,
214 P_ORIGINAL_RECIPIENT =>
215 l_original_recipient,
216 P_OVERRIDING_COMMENTS =>
217 l_overriding_comments,
218 P_SIGNATURE_ID => l_psig_signature_id,
219 P_SIGNATURE_SEQUENCE
220 =>p_signaturesequence(i),
221 P_ADHOC_STATUS => 'ADDED',
222 P_ERROR=>L_ERROR,
223 P_ERROR_MSG=>l_error_msg);
224 --Bug 2674799 : end
225
226 IF (nvl(l_error,0) >0) then
227 RAISE EDR_PSIG_ERROR;
228 END IF;
229 end if;
230 end if;
231 end loop;
232 commit;
233 X_ERROR := 0;
234 EXCEPTION
235 WHEN EDR_PSIG_ERROR THEN
236 ROLLBACK;
237 X_ERROR := l_ERROR;
238 X_ERROR_MSG := l_error_msg;
239
240 WHEN OTHERS THEN
241 ROLLBACK;
242 X_ERROR:=SQLCODE;
243 X_ERROR_MSG:=SQLERRM;
244
245
246 END UPDATE_SIGNERLIST;
247
248 END EDR_ADHOC_USER_PVT;