1 PACKAGE BODY EDR_PSIG_MSCA_PAGE_FLOW AS
2 /* $Header: EDRVMPFB.pls 120.1.12000000.1 2007/01/18 05:56:26 appldev ship $ */
3
4
5 --Process the signature response
6
7 PROCEDURE PROCESS_RESPONSE( p_event_id IN NUMBER,
8 p_erecord_id IN NUMBER,
9 p_user_name IN VARCHAR2,
10 p_action_code IN VARCHAR2,
11 p_action_meaning IN VARCHAR2,
12 p_sign_sequence IN NUMBER,
13 p_signature_param_names IN FND_TABLE_OF_VARCHAR2_255,
14 p_signature_param_values IN FND_TABLE_OF_VARCHAR2_255,
15 p_sig_param_display_names IN FND_TABLE_OF_VARCHAR2_255,
16 x_error_code OUT NOCOPY NUMBER,
17 x_error_msg OUT NOCOPY VARCHAR2)
18 IS
19 l_signer_code VARCHAR2(30) ;
20 l_signing_reason_code VARCHAR2(32) ;
21 l_signer_comment VARCHAR2(4000);
22 l_sign_sequence NUMBER;
23 l_count NUMBER;
24 l_error_num NUMBER;
25 l_error_msg VARCHAR2(1000);
26 l_sig_id NUMBER;
27 l_param_table EDR_PSIG.params_table;
28 POST_SIGNATURE_ERROR EXCEPTION;
29 EDR_PSIG_DOC_ERR EXCEPTION;
30 EDR_PSIG_OUT_OF_SEQUENCE EXCEPTION;
31 l_status VARCHAR2(20);
32
33 pragma AUTONOMOUS_TRANSACTION;
34
35 BEGIN
36
37 --Validate the signature sequence
38 SELECT min(signature_sequence) into l_sign_sequence
39 FROM edr_esignatures
40 WHERE EVENT_ID = p_event_id
41 and SIGNATURE_STATUS = 'PENDING';
42
43 --If the sequence is out of order, then raise an error.
44 IF l_sign_sequence <> p_sign_sequence THEN
45 raise EDR_PSIG_OUT_OF_SEQUENCE;
46 END IF;
47
48 --Loop through each signature parameter.
49 FOR i IN p_signature_param_names.first..p_signature_param_names.last LOOP
50 l_param_table(i).param_name := p_signature_param_names(i);
51 l_param_table(i).param_value := p_signature_param_values(i);
52
53 IF l_param_table(i).param_name = 'WF_SIGNER_TYPE' THEN
54 --We need both the signer code and meaning
55 --Code is required for Workflow action history region.
56 --Meaning is required for evidence store.
57 l_signer_code := l_param_table(i).param_value;
58 EDR_STANDARD.GET_MEANING('EDR_SIGNATURE_TYPES', l_signer_code, l_param_table(i).param_value);
59
60 ELSIF l_param_table(i).param_name = 'REASON_CODE' THEN
61 --We need both signing reason code and meaning.
62 --Code is required for workflow action history region.
63 --Meaning is required for evidence store.
64 l_signing_reason_code := l_param_table(i).param_value;
65 EDR_STANDARD.GET_MEANING('EDR_SIGNING_REASONS', l_signing_reason_code, l_param_table(i).param_value);
66
67 ELSIF l_param_table(i).param_name = 'SIGNERS_COMMENT' THEN
68 l_signer_comment := l_param_table(i).param_value;
69 END IF;
70
71 l_param_table(i).Param_displayname := p_sig_param_display_names(i);
72 END LOOP;
73
74 --Update EDR_ESIGNATURES table with the signature details.
75 --As this table is associated with workflow, the signer code and signer reason
76 --code is passed.
77 UPDATE EDR_ESIGNATURES
78 SET SIGNATURE_STATUS = p_action_code ,
79 SIGNATURE_TYPE = l_SIGNER_code,
80 SIGNATURE_REASON_CODE = l_signing_reason_code,
81 SIGNATURE_TIMESTAMP = SYSDATE,
82 SIGNER_COMMENTS = l_signer_comment
83 WHERE EVENT_ID = p_event_id
84 AND user_name = p_user_name
85 AND SIGNATURE_SEQUENCE = p_sign_sequence;
86
87
88 --Post the signature details into the evidence store for the e-record.
89 -- Bug 4190358 : Start
90 -- The Evidence store id should be passed as Null as this is not the document id we maintain.
91
92 EDR_PSIG.postSignature(P_DOCUMENT_ID =>p_erecord_id,
93 P_EVIDENCE_STORE_ID => null,
94 P_USER_NAME => p_user_name,
95 P_USER_RESPONSE => p_action_meaning,
96 P_SIGNATURE_ID => l_sig_id,
97 P_ERROR => l_error_num,
98 P_ERROR_MSG => l_error_msg);
99
100
101 -- Bug 4190358 : End
102 --Raise an error based on the value of l_error_num
103 if l_error_num is not null then
104 raise POST_SIGNATURE_ERROR;
105 end if;
106
107 --Post the signature parameters to the evidence store.
108 --The signature parameters would contain the signer meaning
109 --and the signining reason meaning.
110 EDR_PSIG.postSignatureParameter(P_SIGNATURE_ID => l_sig_id,
111 P_PARAMETERS => l_param_table,
112 P_ERROR => l_error_num,
113 P_ERROR_MSG => l_error_msg);
114
115 if l_error_num is not null then
116 raise POST_SIGNATURE_ERROR;
117 end if;
118
119 --Update the document status in evidence store based on the action code.
120 IF (p_erecord_id IS NOT NULL AND p_action_code = 'REJECTED') THEN
121 EDR_PSIG.changeDocumentStatus(P_DOCUMENT_ID => p_erecord_id,
122 P_STATUS => p_action_code,
123 P_ERROR => l_error_num,
124 P_ERROR_MSG => l_error_msg);
125 IF l_ERROR_NUM IS NOT NULL THEN
126 RAISE EDR_PSIG_DOC_ERR;
127 END IF;
128
129 END IF;
130
131 COMMIT;
132
133 EXCEPTION
134 WHEN EDR_PSIG_OUT_OF_SEQUENCE THEN
135 ROLLBACK;
136 x_error_code := SQLCODE;
137 FND_MESSAGE.SET_NAME('EDR','EDR_MSCA_SIG_OUT_OF_SEQUENCE');
138 x_error_msg := FND_MESSAGE.get;
139 WHEN OTHERS THEN
140 ROLLBACK;
141 x_error_msg := l_error_msg;
142 x_error_code := l_error_num;
143 APP_EXCEPTION.RAISE_EXCEPTION;
144 END PROCESS_RESPONSE;
145
146
147
148 --This procedure is used to process the cancel response in pageflow.
149 PROCEDURE PROCESS_CANCEL(p_erecord_id IN NUMBER,
150 p_itemtype IN VARCHAR2,
151 p_itemkey IN VARCHAR2,
152 x_error_code OUT NOCOPY NUMBER,
153 x_error_msg OUT NOCOPY VARCHAR2)
154 IS
155
156 pragma AUTONOMOUS_TRANSACTION;
157
158 BEGIN
159
160 --Update the document status in evidence store for the e-record.
161 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => p_erecord_id,
162 P_STATUS => 'CANCEL' ,
163 P_ERROR => x_error_code,
164 P_ERROR_MSG => x_error_msg);
165
166 --Complete the pageflow block activity.
167 PROCESS_MSCA_BLOCKED_ACTIVITY(p_itemtype => p_itemtype,
168 p_itemkey => p_itemkey,
169 p_action => 'DONE',
170 x_error_code => x_error_code ,
171 x_error_msg => x_error_msg);
172
173 COMMIT;
174 EXCEPTION
175 WHEN OTHERS THEN
176 ROLLBACK;
177 x_error_code := SQLCODE;
178 x_error_msg := SQLERRM;
179 APP_EXCEPTION.RAISE_EXCEPTION;
180 END PROCESS_CANCEL;
181
182
183 --This procedure is used to process the 'DEFER' response in pageflow.
184 PROCEDURE PROCESS_DEFER(p_erecord_id IN NUMBER,
185 p_itemtype IN VARCHAR2,
186 p_itemkey IN VARCHAR2,
187 x_error_code OUT NOCOPY NUMBER,
188 x_error_msg OUT NOCOPY VARCHAR2)
189 is
190
191 pragma AUTONOMOUS_TRANSACTION;
192
193 BEGIN
194
195 --Update the evidence store document status to 'PENDING'
196 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => p_erecord_id,
197 P_STATUS => 'PENDING' ,
198 P_ERROR => x_error_code,
199 P_ERROR_MSG => x_error_msg);
200
201 --Process the MSCA Block activity for "DEFER" response.
202 PROCESS_MSCA_BLOCKED_ACTIVITY(p_itemtype => p_itemtype,
203 p_itemkey => p_itemkey,
204 p_action => 'DEFER',
205 x_error_code => x_error_code ,
206 x_error_msg => x_error_msg);
207
208 COMMIT;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 ROLLBACK;
213 x_error_code := SQLCODE;
214 x_error_msg := SQLERRM;
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END PROCESS_DEFER;
217
218
219 --This procedure is used to process the pageflow block activity defined for MSCA.
220 PROCEDURE PROCESS_MSCA_BLOCKED_ACTIVITY(p_itemtype IN VARCHAR2,
221 p_itemkey IN VARCHAR2,
222 p_action IN VARCHAR2,
223 x_error_code OUT NOCOPY NUMBER,
224 x_error_msg OUT NOCOPY VARCHAR2)
225
226 IS
227
228 BEGIN
229
230 --Move pageflow to MSCA Block activity.
231 FND_WF_ENGINE.COMPLETEACTIVITY(p_itemtype, p_itemkey, 'MSCA', p_action);
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 x_error_code := SQLCODE;
236 x_error_msg := SQLERRM;
237 APP_EXCEPTION.RAISE_EXCEPTION;
238
239 END PROCESS_MSCA_BLOCKED_ACTIVITY;
240
241
242 --Wrapper procedure over EDR_PSIG.CLOSEDOCUMENT
243 PROCEDURE CLOSE_DOCUMENT(P_DOCUMENT_ID IN NUMBER,
244 X_ERROR OUT NOCOPY NUMBER,
245 X_ERROR_MSG OUT NOCOPY VARCHAR2
246 )
247
248 is
249
250 pragma AUTONOMOUS_TRANSACTION;
251
252 BEGIN
253
254 EDR_PSIG.ClOSEDOCUMENT(P_DOCUMENT_ID => P_DOCUMENT_ID,
255 P_ERROR => X_ERROR,
256 P_ERROR_MSG => X_ERROR_MSG
257 );
258 COMMIT;
259
260 EXCEPTION WHEN OTHERS THEN
261 ROLLBACK;
262 x_error := SQLCODE;
263 x_error_msg := SQLERRM;
264
265 APP_EXCEPTION.RAISE_EXCEPTION;
266
267 END CLOSE_DOCUMENT;
268
269 END EDR_PSIG_MSCA_PAGE_FLOW;
[Home] [Help]