[Home] [Help]
PACKAGE BODY: APPS.EDR_FWK_VERIFY
Source
1 PACKAGE BODY edr_FWK_VERIFY AS
2 /* $Header: EDRFWKVB.pls 120.0.12000000.1 2007/01/18 05:53:09 appldev ship $
3
4
5 /* Verify if a notificaiton is Require or Not */
6
7 PROCEDURE CHECK_REQUIREMENT
8 (
9 p_itemtype IN VARCHAR2,
10 p_itemkey IN VARCHAR2,
11 p_actid IN NUMBER,
12 p_funcmode IN VARCHAR2,
13 p_resultout OUT NOCOPY VARCHAR2
14 ) IS
15
16 L_event_key varchar2(240);
17 l_fwk_test_no varchar2(200);
18 l_requester varchar2(240);
19 l_count number;
20 Cursor c1 is select count(*) from fnd_attached_documents
21 where ENTITY_NAME='EDR_FWK_TEST_B' and
22 pk1_value=l_event_key;
23 BEGIN
24 --Bug 4073809 : start
25 L_event_key := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
26 itemkey => p_itemkey,
27 aname => 'EVENT_KEY');
28 --Bug 4073809 : end
29
30 -- Bug : 3499311 Start - Specified Number Format in TO_NUMBER
31 Select fwk_test_no,last_updated_by into l_fwk_test_no,l_requester from edr_fwk_test_vl
35 where user_id=l_requester;
32 where fwk_test_id = to_number(l_event_key,'999999999999.999999');
33 -- Bug : 3499311 - End
34 select user_name into l_requester from fnd_user
36
37 wf_engine.setitemattrtext(itemtype => p_itemtype,
38 itemkey => p_itemkey,
39 aname => 'FWK_TEST_NO',
40 avalue => l_fwk_test_no);
41 wf_engine.setitemattrtext(itemtype => p_itemtype,
42 itemkey => p_itemkey,
43 aname => 'REQUESTER',
44 avalue => l_requester);
45
46 /* set Attachment Attribute */
47 /* Bug Fix 3060818. Create Attachments attribute only if a Valid Attachment is available */
48 /* Begin Bug Fix 3060818 */
49 open c1;
50 fetch c1 into l_count;
51 close c1;
52 if l_count > 0 THEN
53 wf_engine.setitemattrtext(itemtype => p_itemtype,
54 itemkey => p_itemkey,
55 aname => 'ATTACHMENTS',
56 --Bug 4006844: Start
57 avalue => 'FND:entity=EDR_FWK_TEST_B'||'&'||'pk1name=FWK_TEST_ID'||'&'||'pk1value='||l_event_key||'&'||'categories=ERES');
58 --Bug 4006844: End
59 END IF;
60 /* End Bug fix 3060818 */
61 p_resultout:='COMPLETE:Y';
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 P_resultout:='COMPLETE:N';
66 WHEN OTHERS THEN
67 WF_CORE.CONTEXT ('EDR_FWK_VERIFY','CHECK_REQUIREMENT',p_itemtype,p_itemkey,SQLERRM);
68 raise;
69
70 END CHECK_REQUIREMENT;
71
72 PROCEDURE SET_ATTRIBUTES
73 (
74 p_itemtype IN VARCHAR2,
75 p_itemkey IN VARCHAR2,
76 p_actid IN NUMBER,
77 p_funcmode IN VARCHAR2,
78 p_resultout OUT NOCOPY VARCHAR2
79 ) IS
80 BEGIN
81 IF P_FUNCMODE = 'RESPOND' THEN
82 wf_engine.setitemattrtext(itemtype => p_itemtype,
83 itemkey => p_itemkey,
84 aname => 'NOTIFICATION_ID',
85 avalue => wf_engine.context_nid);
86
87 END IF;
88 EXCEPTION
89 WHEN OTHERS THEN
90
91 WF_CORE.CONTEXT ('EDR_FWK_VERIFY','SET_ATTRIBUTES',p_itemtype,p_itemkey,SQLERRM);
92 raise;
93
94 END SET_ATTRIBUTES;
95
96 PROCEDURE UPDATE_EVIDENCE
97 (p_itemtype IN VARCHAR2,
98 p_itemkey IN VARCHAR2,
99 p_actid IN NUMBER,
100 p_funcmode IN VARCHAR2,
101 p_resultout OUT NOCOPY VARCHAR2
102 ) IS
103
104 l_Event_name varchar2(240);
105 l_Event_key varchar2(240);
106 l_requester varchar2(240);
107 l_nid varchar2(240);
108 l_fwk_test_no varchar2(240);
109
110 l_doc_id number;
111 l_error number;
112 l_error_msg varchar2(4000);
113 l_doc_params EDR_EvidenceStore_PUB.params_tbl_type;
114 l_sig_id number;
115 l_sign_params EDR_EvidenceStore_PUB.params_tbl_type;
116 l_result varchar2(1000);
117 l_ret_status varchar2(30);
118 l_msg_count number;
119 l_msg_data varchar2(1000);
120 BEGIN
121
122 --Bug 4073809 : start
123 l_Event_name :=wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
124 itemkey => p_itemkey,
125 aname => 'EVENT_NAME');
126 l_Event_key :=wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
127 itemkey => p_itemkey,
128 aname => 'EVENT_KEY');
129 l_requester :=wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
130 itemkey => p_itemkey,
131 aname => 'REQUESTER');
132 l_nid :=wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
133 itemkey => p_itemkey,
134 aname => 'NOTIFICATION_ID');
135 l_fwk_test_no :=wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
136 itemkey => p_itemkey,
137 aname => 'FWK_TEST_NO');
138
139 --Bug 4073809 : end
140
141 IF P_FUNCMODE ='RUN' THEN
142 l_result:= wf_notification.getattrtext(l_nid,'RESULT');
143 l_doc_params(1).param_name:='PSIG_USER_KEY_LABEL';
144 l_doc_params(1).param_value:='WF-'||FND_MESSAGE.GET_STRING('EDR','EDR_FWK_TEST_LBL');
145 l_doc_params(1).param_displayname:='Identifier Label';
146 l_doc_params(2).param_name:='PSIG_USER_KEY_VALUE';
147 l_doc_params(2).param_value:='WF-'||l_fwk_test_no;
148 l_doc_params(2).param_displayname:='Identifier value';
149
150 EDR_EvidenceStore_PUB.Capture_Signature (
151 p_api_version => 1.0,
152 p_init_msg_list => FND_API.G_TRUE,
153 p_commit => FND_API.G_FALSE,
154 x_return_status => l_ret_status,
155 x_msg_count => l_msg_count,
156 x_msg_data => l_msg_data,
157 P_PSIG_XML => NULL,
158 P_PSIG_DOCUMENT => NULL,
159 P_PSIG_DocFormat => 'text/plain',
160 P_PSIG_REQUESTER => l_requester,
161 P_PSIG_SOURCE => 'DB',
162 P_EVENT_NAME => l_event_name,
163 P_EVENT_KEY => l_event_key,
164 P_WF_Notif_ID => l_nid,
165 x_DOCUMENT_ID => l_doc_id,
166 p_doc_parameters_tbl => l_doc_params,
167 p_user_name => l_requester,
168 p_original_recipient => l_requester,
169 p_overriding_comment => null,
170 x_signature_id => l_sig_id,
171 p_evidenceStore_id => wf_engine.getitemattrtext(p_itemtype, p_itemkey,'#WF_SIG_ID'),
172 p_user_response => wf_notification.getattrtext(l_nid,'RESULT'),
173 p_sig_parameters_tbl => l_sign_params );
174
175 /* Close Document if SUCCESS*/
176
177 IF l_result='APPROVED' then
178 EDR_PSIG.closeDocument( P_DOCUMENT_ID => l_doc_id,
179 P_ERROR => l_error,
180 P_ERROR_MSG => l_error_msg);
181 ELSIF l_result='REJECTED' then
182
183 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => l_doc_id,
184 P_STATUS => 'REJECTED',
185 P_ERROR => l_error,
186 P_ERROR_MSG => l_error_msg);
187 END IF;
188 END IF;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 WF_CORE.CONTEXT ('EDR_FWK_VERIFY','UPDATE_EVIDENCE',p_itemtype,p_itemkey,SQLERRM);
193 raise;
194
195 END UPDATE_EVIDENCE;
196
197
198
199 -- --------------------------
200 -- Procedure : Test_EvidenceStore
201 -- Function : test edr_EvidenceStore_pub.Capture_Signature
202 -- version : 23-Jul-03 jianliu created
203 -- Usage : Test_EvidenceStore('EDRPSIGF', '3157', 44145, 'oracle.apps.edr.amevar.update', '26', ostr)
204 -- --------------------------
205
206 PROCEDURE Test_EvidenceStore (
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2,
209 p_itemtype IN VARCHAR2,
210 p_itemkey IN VARCHAR2,
211 p_notif_id IN NUMBER,
212 p_event_name IN VARCHAR2,
213 p_event_key IN VARCHAR2,
214 x_resultout OUT NOCOPY VARCHAR2 )
215 IS
216 l_Event_name varchar2(240);
217 l_Event_key varchar2(240);
218 l_requester varchar2(240);
219 l_nid varchar2(240);
220 l_doc_id number;
221 l_error number;
222 l_error_msg varchar2(4000);
223 l_doc_params EDR_EvidenceStore_PUB.params_tbl_type;
224 l_sig_id number;
225 l_sign_params EDR_EvidenceStore_PUB.params_tbl_type;
226 l_result varchar2(1000);
227
228 l_ret_status varchar2(30);
229 l_msg_count number;
230 l_msg_data varchar2(1000);
231 l_ret_sig_id number;
232 BEGIN
233 begin
234 l_Event_name := wf_engine.GETITEMATTRTEXT( itemtype => p_itemtype,
235 itemkey => p_itemkey, aname => 'EVENT_NAME');
236 exception
237 when others then
238 l_event_name := p_event_name;
239 end;
240 begin
241 l_Event_key := wf_engine.GETITEMATTRTEXT( itemtype => p_itemtype,
242 itemkey => p_itemkey, aname => 'EVENT_KEY');
243 exception
244 when others then
245 l_event_key := p_event_key;
246 end;
247 begin
248 l_requester := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
249 itemkey => p_itemkey, aname => 'REQUESTER');
250 exception
251 when others then
252 l_requester := 'MFG';
253 end;
254 begin
255 l_nid := wf_engine.GETITEMATTRTEXT(itemtype => p_itemtype,
256 itemkey => p_itemkey, aname => 'NOTIFICATION_ID');
257 exception
258 when others then
259 l_nid := TO_CHAR(p_notif_id);
260 end;
261
262
263 begin
264 l_result:= wf_notification.getattrtext(l_nid,'RESULT');
265 exception
266 when others then
267 l_result := 'PENDING';
268 end;
269
270 l_doc_params(1).param_name := 'PSIG_USER_KEY_LABEL';
271 l_doc_params(1).param_value := 'Transaction, Input Variable';
272 l_doc_params(1).param_displayname := 'Identifier Label';
273 l_doc_params(2).param_name := 'PSIG_USER_KEY_VALUE';
274 l_doc_params(2).param_value := 'EDR ERES File Approval, ESIG_REQUIRED';
275 l_doc_params(2).param_displayname := 'Identifier value';
276
277 wf_log_pkg.string(6, 'EDR_PSIG_rule.psig_rule','Posting Document Parameters');
278
279 l_sign_params(1).param_name := 'WF_NOTE';
280 l_sign_params(1).param_value := 'I only wanna test capture_signature';
281 l_sign_params(1).param_displayname := 'Signer Comments';
282
283 l_sign_params(2).param_name := 'REASON_CODE';
284 l_sign_params(2).param_value := NULL;
285 l_sign_params(2).param_displayname := 'Signing Reason';
286
287 l_sign_params(3).param_name := 'WF_SIGNER_TYPE';
288 l_sign_params(3).param_value := NULL;
289 l_sign_params(3).param_displayname := 'Signature Type ';
290
291 EDR_EvidenceStore_PUB.Capture_Signature (
292 p_api_version => p_api_version,
293 p_init_msg_list => p_init_msg_list,
294 p_commit => FND_API.G_FALSE,
295 x_return_status => l_ret_status,
296 x_msg_count => l_msg_count,
297 x_msg_data => l_msg_data,
298 P_PSIG_XML => NULL,
299 P_PSIG_DOCUMENT => NULL,
300 P_PSIG_DocFormat => 'text/plain',
301 P_PSIG_REQUESTER => l_requester,
302 P_PSIG_SOURCE => 'DB',
303 P_EVENT_NAME => l_event_name,
304 P_EVENT_KEY => l_event_key,
305 P_WF_Notif_ID => l_nid,
306 x_DOCUMENT_ID => l_doc_id,
307 p_doc_parameters_tbl => l_doc_params,
308 p_user_name => l_requester,
309 p_original_recipient => l_requester,
310 p_overriding_comment => null,
311 x_signature_id => l_ret_sig_id,
312 p_evidenceStore_id => wf_engine.getitemattrtext(p_itemtype, p_itemkey,'#WF_SIG_ID'),
313 p_user_response => wf_notification.getattrtext(l_nid,'RESULT'),
314 p_sig_parameters_tbl => l_sign_params );
315 x_resultout := l_result;
316 EXCEPTION
317 WHEN OTHERS THEN
318 WF_CORE.CONTEXT ('EDR_FWK_VERIFY','UPDATE_EVIDENCE',p_itemtype,p_itemkey,SQLERRM);
319 raise;
320 END Test_EvidenceStore;
321
322
323
324 end EDR_FWK_VERIFY;