[Home] [Help]
PACKAGE BODY: APPS.EDR_ISIGN_CHECKLIST_PVT
Source
1 PACKAGE BODY EDR_ISIGN_CHECKLIST_PVT AS
2 /* $Header: EDRVISCB.pls 120.8.12000000.1 2007/01/18 05:56:21 appldev ship $ */
3
4 -- GLOBAL decleration
5
6 G_ENTITY_NAME constant varchar2(10) := 'ERECORD';
7
8
9 -- --------------------------------------
10 -- API name : IS_CHECKLIST_REQUIRED
11 -- Type : Public
12 -- Pre-reqs : None
13 -- procedue : return Y/N based on checklsit steup and if Y, returns checlist name and checklist version
14 -- Parameters
15 -- IN : p_event_name VARCHAR2 event name, eg oracle.apps.gmi.item.create
16 -- p_event_key VARCHAR2 event key, eg ItemNo3125
17 -- OUT : x_isRequired_checklist VARCHAR2 Checklist status
18 -- OUT : x_checklist_name VARCHAR2 Checklist Name
19 -- OUT : x_checklist_ver VARCHAR2 Checklist Version
20
21 -- ---------------------------------------
22
23
24 PROCEDURE IS_CHECKLIST_REQUIRED (
25 p_event_name IN varchar2,
26 p_event_key IN varchar2,
27 x_isRequired_checklist OUT NOCOPY VARCHAR2,
28 x_checklist_name OUT NOCOPY VARCHAR2,
29 x_checklist_ver OUT NOCOPY VARCHAR2) IS
30
31 l_event_status varchar2(100);
32 l_sub_status varchar2(100);
33 l_sub_guid varchar2(4000);
34 evt wf_event_t;
35 l_application_id number;
36 l_application_code varchar2(32);
37 l_return_status varchar2(32);
38 l_application_name varchar2(240);
39 l_ame_transaction_Type varchar2(240);
40 l_transaction_name varchar2(240);
41 l_ruleids ame_util.idList;
42 l_rulenames ame_util.stringList;
43 l_rulevalues EDR_STANDARD.ameruleinputvalues;
44 approverList ame_util.approversTable;
45
46 l_isRequired_checklist varchar2(1);
47 l_rule_checklist varchar2(1);
48 l_temp_template varchar2(200);
49 l_temp_template_ver varchar2(200);
50 CURSOR GET_EVT_SUBSCRIPTION_DETAILS IS
51 select b.guid,A.status,b.status
52 from
53 wf_events a, wf_event_subscriptions b
54 where a.GUID = b.EVENT_FILTER_GUID
55 and a.name = p_event_name
56 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
57 and b.STATUS = 'ENABLED'
58 --Bug No 4912782- Start
59 and b.source_type = 'LOCAL'
60 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
61 --Bug No 4912782- End
62 l_no_enabled_eres_sub NUMBER;
63 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
64 BEGIN
65
66 --Bug 4074173 : GSCC Warning
67 l_isRequired_checklist :='N';
68
69 SAVEPOINT CHECKLIST_REQUIRED;
70 x_isRequired_checklist:='N';
71 x_checklist_name:=NULL;
72 x_checklist_ver:=NULL;
73
74 -- Verify is more than one active ERES subscriptions are present
75 --
76 select count(*) INTO l_no_enabled_eres_sub
77 from
78 wf_events a, wf_event_subscriptions b
79 where a.GUID = b.EVENT_FILTER_GUID
80 and a.name = p_event_name
81 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
82 and b.STATUS = 'ENABLED'
83 --Bug No 4912782- Start
84 and b.source_type = 'LOCAL'
85 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
86 --Bug No 4912782- End
87 IF l_no_enabled_eres_sub > 1 THEN
88 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
89 ELSE
90 OPEN GET_EVT_SUBSCRIPTION_DETAILS;
91 FETCH GET_EVT_SUBSCRIPTION_DETAILS INTO l_sub_guid,l_Event_status,l_sub_status;
92 CLOSE GET_EVT_SUBSCRIPTION_DETAILS;
93 END IF;
94
95 wf_event_t.initialize(evt);
96 evt.setSendDate(sysdate);
97 evt.setEventName(p_event_name);
98 evt.setEventKey(p_event_key);
99 -- Bug 5639849 : Starts
100 -- No need loading all subscription parameters to event, just use
101 -- edr_indexed_xml_util API to get ame_transaction _type parameter
102 --l_return_status:=wf_rule.setParametersIntoParameterList(l_sub_guid,evt);
103
104 --IF l_return_status='SUCCESS' THEN
105 /* Check for User Defined Parameters,
106 contains AME transactions Type
107 If Parameters are not specified, Assume Event name to be AME transaction Type
108
109 */
110 l_ame_transaction_type:= NVL(EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_AME_TRANSACTION_TYPE',l_sub_guid), evt.getEventName( ));
111 -- Bug 5639849 : Ends
112 /* AME Processing */
113 /* Select APPLICATION_ID of the Event. This is required by AME. Assumption made here
114 is OWNER_TAG will always be set to application Short Name*/
115
116 SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,l_application_code
117 FROM FND_APPLICATION
118 WHERE APPLICATION_SHORT_NAME in (SELECT OWNER_TAG from WF_EVENTS
119 WHERE NAME=evt.getEventName( ));
120
121 /* AME Enhancement Code. Determine if singature is need or not and also get approvers */
122
123 /* This Code should be uncommented when the SSWA AME forms move to opmeres */
124
125 -- Bug 5167817 : start
126 /* AME_API.GETAPPROVERSANDRULES3
127 ( APPLICATIONIDIN => l_application_Id,
128 TRANSACTIONIDIN => evt.getEventKey( ),
129 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName( )),
130 APPROVERSOUT => approverList,
131 RULEIDSOUT => l_ruleids,
132 RULEDESCRIPTIONSOUT=> l_rulenames
133 ); */
134
135 --Bug 5287504: Start
136 BEGIN
137 AME_API3.GETAPPLICABLERULES3
138 ( APPLICATIONIDIN => l_application_Id,
139 TRANSACTIONIDIN => evt.getEventKey( ),
140 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName( )),
141 RULEIDSOUT => l_ruleids,
142 RULEDESCRIPTIONSOUT=> l_rulenames
143 );
144 EXCEPTION
145 WHEN OTHERS THEN
146 FND_MESSAGE.SET_NAME('EDR','EDR_AME_SETUP_ERR');
147 FND_MESSAGE.SET_TOKEN('TXN_TYPE',nvl(l_ame_transaction_type,evt.getEventName()));
148 FND_MESSAGE.SET_TOKEN('ERR_MSG',sqlerrm);
149 APP_EXCEPTION.RAISE_EXCEPTION;
150 END;
151 --Bug 5287504: End
152
153 -- Bug 5167817 : end
154 select application_name into l_application_name
155 from ame_Calling_Apps
156 where FND_APPLICATION_ID=l_application_id
157 and TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,evt.getEventName( ))
158 --Bug 4652277: Start
159 --and end_Date is null;
160 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
161 --Bug 4652277: End
162
163
164
165 /* check if any rules are satisfied, if not pick the variables from transaction */
166 if l_ruleids.count < 1 then
167 l_ruleids(1) := -1;
168 l_rulenames(1) := Null;
169 END IF;
170
171 for i in 1..l_ruleids.count loop
172
173 -- Bug 3214495 : Start
174
175 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES(transactiontypeid =>NVL(l_ame_transaction_type,evt.getEventName( )),
176 ameruleid =>l_ruleids(i),
177 amerulename=>l_rulenames(i),
178 ameruleinputvalues=>l_rulevalues);
179 -- Bug 3214495 : End
180 if l_rulevalues.count > 0 then
181 for i in 1..l_rulevalues.count loop
182 if l_rulevalues(i).input_name = 'CHECKLIST_REQUIRED' then
183 if (l_isRequired_checklist='N' and l_rulevalues(i).input_value ='Y') then
184 l_rule_checklist:= l_rulevalues(i).input_value;
185 end if;
186 elsif l_rulevalues(i).input_name = 'CHECKLIST_TEMPLATE' then
187 l_temp_template:= l_rulevalues(i).input_value;
188 elsif l_rulevalues(i).input_name = 'CHECKLIST_TEMPLATE_VER' then
189 l_temp_template_ver:= l_rulevalues(i).input_value;
190 end if;
191 /* Assign Appropriate values based on most stringent rules*/
192 if l_rule_checklist = 'Y' then
193 x_isRequired_checklist:=l_rule_checklist;
194 x_checklist_name:=l_temp_template;
195 x_checklist_ver:= l_temp_template_ver;
196 end if;
197 end loop;
198 end if;
199 END LOOP;
200 -- bug 5639849 : Starts
201 -- Commented the if return_status statement
202 -- END IF;
203 -- bug 5639849 : End if
204
205 -- Following statement clears all lock aquired by this session
206
207
208 ROLLBACK TO CHECKLIST_REQUIRED;
209
210 EXCEPTION
211 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
212 x_isRequired_checklist:='N';
213 x_checklist_name:=NULL;
214 x_checklist_ver:=NULL;
215 ROLLBACK TO CHECKLIST_REQUIRED;
216 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRP_ERR');
217 fnd_message.set_token( 'EVENT', p_event_name);
218 --Bug 4162993: Start
219 APP_EXCEPTION.RAISE_EXCEPTION;
220 --Bug 4162993: End
221 WHEN OTHERS THEN
222 x_isRequired_checklist:='N';
223 x_checklist_name:=NULL;
224 x_checklist_ver:=NULL;
225 ROLLBACK TO CHECKLIST_REQUIRED;
226 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
227 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
228 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_ISIGN_CHECKLIST_PVT');
229 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','IS_CHECKLIST_REQUIRED ');
230 --Bug 4162993: Start
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 --Bug 4162993: End
233 END IS_CHECKLIST_REQUIRED;
234
235
236 -- --------------------------------------
237 -- API name : IS_CHECKLIST_PRESENT
238 -- Type : Private
239 -- Pre-reqs : None
240 -- procedue : Procedure will notify if checklist is availalb for a given file_id
241 -- Parameters
242 -- IN : p_file_id NUMBER file_id of iSign
243 -- OUT : x_checklist_status VARCHAR2 Checklist Status
244 -- Y - Checklist Present
245 -- N - Checklist not avalable
246 -- ---------------------------------------
247
248
249
250 PROCEDURE IS_CHECKLIST_PRESENT (
251 p_file_id IN NUMBER,
252 x_checklist_Status OUT NOCOPY VARCHAR2) IS
253
254 Cursor c1 is select count(*) from fnd_attached_documents
255 where ENTITY_NAME='EDR_FILES_B' and
256 pk1_value=p_file_id and
257 document_id in (select document_id from fnd_documents_vl where category_id in
258 (select category_id
259 from fnd_document_categories_vl
260 where name = 'EDR_ISIGN_ADDL_FILES'));
261 l_count number;
262 BEGIN
263
264 OPEN c1;
265 fetch c1 into l_count;
266 CLOSE c1;
267
268 if l_count > 0 then
269 x_checklist_Status:='Y';
270 else
271 x_checklist_Status:='N';
272 end if;
273
274 exception
275 when others then
276 x_checklist_Status:='N';
277 END IS_CHECKLIST_PRESENT;
278
279
280 -- --------------------------------------
281 -- API name : ATTACH_CHECKLIST
282 -- Type : Public
283 -- Pre-reqs : None
284 -- procedue : Procedure to Attach checklist to evidence store entity if available
285 -- Parameters
286 -- IN : p_file_id NUMBER file_id of iSign
287 -- OUT : x_return_status VARCHAR2 Attachment Status
288 -- S - Successful
289 -- E - Error
290 -- ---------------------------------------
291
292
293 PROCEDURE ATTACH_CHECKLIST (
294 p_file_id IN NUMBER,
295 x_return_Status OUT NOCOPY VARCHAR2)
296 AS PRAGMA AUTONOMOUS_TRANSACTION;
297
298 l_return_status varchar2(100);
299 l_msg_count number;
300 l_msg_data varchar2(4000);
301 l_erecord_id number;
302 l_category_id NUMBER;
303 l_checklist_status VARCHAR2(1);
304 Begin
305 -- Check if checklsit exists for the file
306 EDR_ISIGN_CHECKLIST_PVT.IS_CHECKLIST_PRESENT(p_file_id =>p_file_id,
307 x_checklist_Status=>l_checklist_Status
308 );
309
310 If l_checklist_status= 'Y' THEN
311
312 -- get the erecord id for the file
313 EDR_STANDARD_PUB.GET_ERECORD_ID(
314 P_API_VERSION => 1.0,
315 P_INIT_MSG_LIST => FND_API.G_FALSE,
316 X_RETURN_STATUS => l_return_status,
317 X_MSG_COUNT => l_msg_count,
318 X_MSG_DATA => l_msg_data,
319 P_EVENT_NAME => 'oracle.apps.edr.file.approve',
320 P_EVENT_KEY => p_file_ID,
321 X_ERECORD_ID => l_erecord_id);
322
323 -- Make an attachemtn to erecord entity
324 IF (L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS and l_erecord_id is not NULL) THEN
325 -- call copy attachment for each category
326 /* select Catgory id of checklist */
327 select category_id into l_category_id
328 from fnd_document_categories_vl
329 where name = 'EDR_ISIGN_ADDL_FILES';
330
331 --Bug 4381237: Start
332 --Change the call to edr attachment API
333 --fnd_attached_documents2_pkg.copy_attachments
334 edr_attachments_grp.copy_attachments
335 --Bug 4381237: End
336 (X_from_entity_name => 'EDR_FILES_B',
337 X_from_pk1_value => p_file_id,
338 X_from_pk2_value => NULL,
339 X_from_pk3_value => NULL,
340 X_from_pk4_value => NULL,
341 X_from_pk5_value => NULL,
342 X_to_entity_name => G_ENTITY_NAME,
343 X_to_pk1_value => l_erecord_id,
344 X_to_pk2_value => null,
345 X_to_pk3_value => null,
346 X_to_pk4_value => null,
347 X_to_pk5_value => null,
348 X_created_by => fnd_global.user_id,
349 X_last_update_login => fnd_global.login_id,
350 X_program_application_id => null,
351 X_program_id => null,
352 X_request_id => null,
353 X_automatically_added_flag => null,
354 X_from_category_id => l_category_id,
355 X_to_category_id => l_category_id);
356 END IF;
357 END IF;
358 COMMIT;
359 x_return_Status:='S';
360 exception
361 when others then
362 ROLLBACK;
363 x_return_Status:='E';
364 END ATTACH_CHECKLIST;
365
366 PROCEDURE DELETE_CHECKLIST (
367 p_file_id IN NUMBER,
368 x_return_Status OUT NOCOPY VARCHAR2) IS
369 l_return_status varchar2(100);
370 l_attach_document_id number;
371 Cursor c1 is select ATTACHED_DOCUMENT_ID from fnd_attached_documents
372 where ENTITY_NAME='EDR_FILES_B' and
373 pk1_value=p_file_id and
374 document_id in (select document_id from fnd_documents_vl where category_id in
375 (select category_id
376 from fnd_document_categories_vl
377 where name = 'EDR_ISIGN_ADDL_FILES'));
378 Begin
379
380 OPEN c1;
381 fetch c1 into l_attach_document_id ;
382 IF c1%FOUND THEN
383 -- Delete Checklist
384 fnd_attached_documents3_pkg.DELETE_ROW(
385 X_ATTACHED_DOCUMENT_ID=> l_attach_document_id ,
386 X_DATATYPE_ID => 6,
387 DELETE_DOCUMENT_FLAG => 'Y');
388 END IF;
389 CLOSE c1;
390
391 x_return_Status:='S';
392 exception
393 when others then
394 x_return_Status:='E';
395 END DELETE_CHECKLIST;
396
397 END EDR_ISIGN_CHECKLIST_PVT;