[Home] [Help]
PACKAGE: APPS.QA_EDR_STANDARD
Source
1 PACKAGE qa_edr_standard AUTHID CURRENT_USER AS
2 /* $Header: qaedrs.pls 115.4 2004/02/18 01:55:12 isivakum noship $
3
4 /* Global Variables */
5
6 G_SIGNATURE_STATUS varchar2(80):=NULL; -- Global variable which hold the status of workflow ('APPROVED','REJECTED')
7
8 /* Global Record Groups */
9
10 Type eventDetails is record(
11 event_name varchar2(240),
12 event_key varchar2(240),
13 key_type VARCHAR2(40)
14 );
15 Type eventQuery is table of eventDetails index by binary_integer;
16
17 --Below from EDR_ERES_EVENT_PUB
18 -- Table of erecord ids
19 TYPE ERECORD_ID_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
20
21 -- Record type to represent an ERES event
22 --Bug 3136403: Start
23 --Change the definition of the record type to denormalize the payload parameters
24 /*
25 TYPE ERES_EVENT_REC_TYPE IS RECORD
26 ( EVENT_NAME VARCHAR2(80) ,
27 EVENT_KEY VARCHAR2(240) ,
28 PAYLOAD FND_WF_EVENT.PARAM_TABLE ,
29 ERECORD_ID NUMBER ,
30 EVENT_STATUS VARCHAR2(20)
31 );
32 */
33
34 TYPE ERES_EVENT_REC_TYPE IS RECORD
35 ( EVENT_NAME VARCHAR2(80) ,
36 EVENT_KEY VARCHAR2(240) ,
37 ERECORD_ID NUMBER ,
38 EVENT_STATUS VARCHAR2(20) DEFAULT NULL ,
39 PARAM_NAME_1 VARCHAR2(30) DEFAULT NULL ,
40 PARAM_VALUE_1 VARCHAR2(2000) DEFAULT NULL ,
41 PARAM_NAME_2 VARCHAR2(30) DEFAULT NULL ,
42 PARAM_VALUE_2 VARCHAR2(2000) DEFAULT NULL ,
43 PARAM_NAME_3 VARCHAR2(30) DEFAULT NULL ,
44 PARAM_VALUE_3 VARCHAR2(2000) DEFAULT NULL ,
45 PARAM_NAME_4 VARCHAR2(30) DEFAULT NULL ,
46 PARAM_VALUE_4 VARCHAR2(2000) DEFAULT NULL ,
47 PARAM_NAME_5 VARCHAR2(30) DEFAULT NULL ,
48 PARAM_VALUE_5 VARCHAR2(2000) DEFAULT NULL ,
49 PARAM_NAME_6 VARCHAR2(30) DEFAULT NULL ,
50 PARAM_VALUE_6 VARCHAR2(2000) DEFAULT NULL ,
51 PARAM_NAME_7 VARCHAR2(30) DEFAULT NULL ,
52 PARAM_VALUE_7 VARCHAR2(2000) DEFAULT NULL ,
53 PARAM_NAME_8 VARCHAR2(30) DEFAULT NULL ,
54 PARAM_VALUE_8 VARCHAR2(2000) DEFAULT NULL ,
55 PARAM_NAME_9 VARCHAR2(30) DEFAULT NULL ,
56 PARAM_VALUE_9 VARCHAR2(2000) DEFAULT NULL ,
57 PARAM_NAME_10 VARCHAR2(30) DEFAULT NULL ,
58 PARAM_VALUE_10 VARCHAR2(2000) DEFAULT NULL ,
59 PARAM_NAME_11 VARCHAR2(30) DEFAULT NULL ,
60 PARAM_VALUE_11 VARCHAR2(2000) DEFAULT NULL ,
61 PARAM_NAME_12 VARCHAR2(30) DEFAULT NULL ,
62 PARAM_VALUE_12 VARCHAR2(2000) DEFAULT NULL ,
63 PARAM_NAME_13 VARCHAR2(30) DEFAULT NULL ,
64 PARAM_VALUE_13 VARCHAR2(2000) DEFAULT NULL ,
65 PARAM_NAME_14 VARCHAR2(30) DEFAULT NULL ,
66 PARAM_VALUE_14 VARCHAR2(2000) DEFAULT NULL ,
67 PARAM_NAME_15 VARCHAR2(30) DEFAULT NULL ,
68 PARAM_VALUE_15 VARCHAR2(2000) DEFAULT NULL ,
69 PARAM_NAME_16 VARCHAR2(30) DEFAULT NULL ,
70 PARAM_VALUE_16 VARCHAR2(2000) DEFAULT NULL ,
71 PARAM_NAME_17 VARCHAR2(30) DEFAULT NULL ,
72 PARAM_VALUE_17 VARCHAR2(2000) DEFAULT NULL ,
73 PARAM_NAME_18 VARCHAR2(30) DEFAULT NULL ,
74 PARAM_VALUE_18 VARCHAR2(2000) DEFAULT NULL ,
75 PARAM_NAME_19 VARCHAR2(30) DEFAULT NULL ,
76 PARAM_VALUE_19 VARCHAR2(2000) DEFAULT NULL ,
77 PARAM_NAME_20 VARCHAR2(30) DEFAULT NULL ,
78 PARAM_VALUE_20 VARCHAR2(2000) DEFAULT NULL
79 );
80 --Bug 3136403: End
81
82 -- A table of ERES Events
83
84 TYPE ERES_EVENT_TBL_TYPE IS TABLE OF ERES_EVENT_REC_TYPE INDEX BY BINARY_INTEGER;
85
86 --Below added for bug 3265661 so Fuad can call this from engwkfwb.pls
87 TYPE params_rec IS RECORD (Param_Name VARCHAR2(80),
88 Param_Value VARCHAR2(4000),
89 Param_displayname varchar2(240));
90
91 TYPE Params_tbl_type IS TABLE of params_rec INDEX by Binary_INTEGER;
92
93 ----------------------------------------------------------------
94
95 -- ---------------------------------------
96
97 PROCEDURE Get_PsigStatus (
98 p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2,
103 p_event_name in varchar2,
104 p_event_key in varchar2,
105 x_psig_status out NOCOPY varchar2 ) ;
106
107
108 -- ---------------------------------------
109
110 PROCEDURE Is_eSig_Required (
111 p_api_version IN NUMBER,
112 p_init_msg_list IN VARCHAR2,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 p_event_name IN varchar2,
117 p_event_key IN varchar2,
118 x_isRequired_eSig OUT NOCOPY VARCHAR2 ) ;
119
120
121
122 -- ---------------------------------------
123
124 PROCEDURE Is_eRec_Required (
125 p_api_version IN NUMBER,
126 p_init_msg_list IN VARCHAR2,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 p_event_name IN varchar2,
131 p_event_key IN varchar2,
132 x_isRequired_eRec OUT NOCOPY VARCHAR2 ) ;
133
134
135 -- ---------------------------------------
136
137 Procedure Get_QueryId_OnEvents (
138 p_api_version IN NUMBER,
139 p_init_msg_list IN VARCHAR2,
140 p_commit IN VARCHAR2,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 p_eventQuery_recTbl IN qa_edr_standard.eventQuery,
145 x_query_id OUT NOCOPY NUMBER );
146
147
148 -- --------------------------------------
149
150 PROCEDURE DISPLAY_DATE_PUB (
151 p_api_version IN NUMBER,
152 p_init_msg_list IN VARCHAR2,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2,
156 P_DATE_IN IN DATE ,
157 x_date_out OUT NOCOPY Varchar2 ) ;
158
159
160 -- ---------------------------------------
161
162 Procedure Is_AuditValue_Old (
163 p_api_version IN NUMBER,
164 p_init_msg_list IN VARCHAR2,
165 x_return_status OUT NOCOPY VARCHAR2,
166 x_msg_count OUT NOCOPY NUMBER,
167 x_msg_data OUT NOCOPY VARCHAR2,
168 p_table_name IN VARCHAR2,
169 p_column_name IN VARCHAR2,
170 p_primKey_name IN VARCHAR2,
171 p_primKey_value IN VARCHAR2,
172 x_isOld_auditValue OUT NOCOPY VARCHAR2 );
173
174 -- ---------------------------------------
175 PROCEDURE Get_Lookup_Meaning (
176 p_api_version IN NUMBER,
177 p_init_msg_list IN VARCHAR2,
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2,
181 p_lookup_type IN VARCHAR2,
182 p_lookup_code IN VARCHAR2,
183 x_lkup_meaning OUT NOCOPY VARCHAR2 );
184
185
186
187
188 ------------------------------------------------------------------
189 --This function is replaced by a new public API formatted procedure
190 --called Get_QueryId_OnEvents
191 --Please use that instead. This function is temporarily still kept here
192 FUNCTION PSIG_QUERY(p_eventQuery QA_EDR_STANDARD.eventQuery) return number;
193
194
195 -- This Function Returns a display date formatted for xml document
196 -- There is a new public API formatted procedure
197 -- called DISPLAY_DATE_PUB . Please use that if you prefer that format
198 PROCEDURE DISPLAY_DATE(P_DATE_IN in DATE , P_DATE_OUT OUT NOCOPY Varchar2) ;
199
200 --for interevents raising
201 PROCEDURE RAISE_ERES_EVENT
202 ( p_api_version IN NUMBER ,
203 p_init_msg_list IN VARCHAR2,
204 p_validation_level IN NUMBER,
205 x_return_status OUT NOCOPY VARCHAR2 ,
206 x_msg_count OUT NOCOPY NUMBER ,
207 x_msg_data OUT NOCOPY VARCHAR2 ,
208 p_child_erecords IN ERECORD_ID_TBL_TYPE ,
209 x_event IN OUT NOCOPY ERES_EVENT_REC_TYPE
210 );
211
212 PROCEDURE RAISE_INTER_EVENT
213 ( p_api_version IN NUMBER ,
214 p_init_msg_list IN VARCHAR2,
215 p_validation_level IN NUMBER,
216 x_return_status OUT NOCOPY VARCHAR2 ,
217 x_msg_count OUT NOCOPY NUMBER ,
218 x_msg_data OUT NOCOPY VARCHAR2 ,
219 x_events IN OUT NOCOPY ERES_EVENT_TBL_TYPE ,
220 x_overall_status OUT NOCOPY VARCHAR2
221 );
222
223
224 PROCEDURE GET_ERECORD_ID ( p_api_version IN NUMBER ,
225 p_init_msg_list IN VARCHAR2 ,
226 x_return_status OUT NOCOPY VARCHAR2 ,
227 x_msg_count OUT NOCOPY NUMBER ,
228 x_msg_data OUT NOCOPY VARCHAR2 ,
229 p_event_name IN VARCHAR2 ,
230 p_event_key IN VARCHAR2 ,
231 x_erecord_id OUT NOCOPY NUMBER );
232
233 --For Acknowledgement (Transaction Acknowledgement) feature
234 procedure SEND_ACKN
235 ( p_api_version IN NUMBER ,
236 p_init_msg_list IN VARCHAR2,
237 x_return_status OUT NOCOPY VARCHAR2 ,
238 x_msg_count OUT NOCOPY NUMBER ,
239 x_msg_data OUT NOCOPY VARCHAR2 ,
240 p_event_name IN VARCHAR2 ,
241 p_event_key IN VARCHAR2 ,
242 p_erecord_id IN NUMBER ,
243 p_trans_status IN VARCHAR2 ,
244 p_ackn_by IN VARCHAR2 ,
245 p_ackn_note IN VARCHAR2 ,
246 p_autonomous_commit IN VARCHAR2
247 );
248
249 -- ----------------------------------------
250 -- API name : wrapper for edr Capture_Signature
251 -- Reference : edr_evidencestore_pub.capture_signature for documentation
252 -- Function : capture the signature for single event
253 -- BUG: Below added for bug 3265661 so Fuad can call this from engwkfwb.pls
254 -- ---------------------------------------
255
256 PROCEDURE Capture_Signature (
257 p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2 default NULL,
259 p_commit IN VARCHAR2 default NULL,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_msg_data OUT NOCOPY VARCHAR2,
263 p_psig_xml IN CLOB default null,
264 p_psig_document IN CLOB default null,
265 p_psig_docFormat IN VARCHAR2,
266 p_psig_requester IN VARCHAR2,
267 p_psig_source IN VARCHAR2,
268 p_event_name IN VARCHAR2,
269 p_event_key IN VARCHAR2,
270 p_wf_notif_id IN NUMBER,
271 x_document_id OUT NOCOPY NUMBER,
272 p_doc_parameters_tbl IN qa_edr_standard.Params_tbl_type,
273 p_user_name IN VARCHAR2,
274 p_original_recipient IN VARCHAR2 default null,
275 p_overriding_comment IN VARCHAR2 default null,
276 x_signature_id OUT NOCOPY NUMBER,
277 p_evidenceStore_id IN NUMBER,
278 p_user_response IN VARCHAR2,
279 p_sig_parameters_tbl IN qa_edr_standard.Params_tbl_type );
280
281 FUNCTION IS_INSTALLED RETURN VARCHAR2;
282 --Above is a new function added as per bug 3253566
283 --it returns 'F' in the stub version and 'T' in real version
284
285 -- ALL OF BELOW 6 Procedures added due to Bug 3447098 as requested by
286 -- ENG-Eres team developer Fuad Abdi
287 -- ----------------------------------------
288 -- API name : Open_Document (Bug 3447098)
289 -- Type : Public
290 -- Function : create a document instance for signature
291 -- : and can associate signatures before closing the docuemnt
292 -- Versions : 1.0 17-Jul-03 created
293 -- ---------------------------------------
294
295 PROCEDURE open_Document (
296 p_api_version IN NUMBER,
297 p_init_msg_list IN VARCHAR2 default NULL,
298 p_commit IN VARCHAR2 default NULL,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 P_PSIG_XML IN CLOB DEFAULT NULL,
303 P_PSIG_DOCUMENT IN CLOB DEFAULT NULL,
304 P_PSIG_DOCUMENTFORMAT IN VARCHAR2 DEFAULT NULL,
305 P_PSIG_REQUESTER IN VARCHAR2,
306 P_PSIG_SOURCE IN VARCHAR2 DEFAULT NULL,
307 P_EVENT_NAME IN VARCHAR2 DEFAULT NULL,
308 P_EVENT_KEY IN VARCHAR2 DEFAULT NULL,
309 p_WF_Notif_ID IN NUMBER DEFAULT NULL,
310 x_DOCUMENT_ID OUT NOCOPY NUMBER );
311
312 -- ----------------------------------------
313 -- API name : Post_DocumentParameter (Bug 3447098)
314 -- Type : Public
315 -- Function : Update a document
316 -- Versions : 1.0 17-Jul-03 created
317 -- ---------------------------------------
318
319 PROCEDURE Post_DocumentParameters (
320 p_api_version IN NUMBER,
321 p_init_msg_list IN VARCHAR2 default NULL,
322 p_commit IN VARCHAR2 default NULL,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_msg_count OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2,
326 p_document_id IN NUMBER,
327 p_doc_parameters_tbl IN qa_edr_standard.Params_tbl_type );
328
329 -- ----------------------------------------
330 -- API name : Close_Document (Bug 3447098)
331 -- Type : Public
332 -- Function : close a document
333 -- Versions : 1.0 17-Jul-03 created
334 -- ---------------------------------------
335
336 PROCEDURE Close_Document (
337 p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2 default NULL,
339 p_commit IN VARCHAR2 default NULL,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 P_DOCUMENT_ID IN NUMBER );
344
345 -- ----------------------------------------
346 -- API name : Request_Signature (Bug 3447098)
347 -- Type : Public
348 -- Function : Update a document
349 -- Versions : 1.0 17-Jul-03 created
350 -- ---------------------------------------
351
352 PROCEDURE Request_Signature (
353 p_api_version IN NUMBER,
354 p_init_msg_list IN VARCHAR2 default NULL,
355 p_commit IN VARCHAR2 default NULL,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2,
359 P_DOCUMENT_ID IN NUMBER,
360 P_USER_NAME IN VARCHAR2,
361 P_ORIGINAL_RECIPIENT IN VARCHAR2 DEFAULT NULL,
362 P_OVERRIDING_COMMENT IN VARCHAR2 DEFAULT NULL,
363 x_signature_id OUT NOCOPY NUMBER );
364
365
366 -- ----------------------------------------
367 -- API name : Post_Signature (Bug 3447098)
368 -- Type : Public
369 -- Function : Update a document
370 -- Versions : 1.0 17-Jul-03 created
371 -- ---------------------------------------
372
373 PROCEDURE Post_Signature (
374 p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2 default NULL,
376 p_commit IN VARCHAR2 default NULL,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_msg_count OUT NOCOPY NUMBER,
379 x_msg_data OUT NOCOPY VARCHAR2,
380 P_DOCUMENT_ID IN NUMBER,
381 p_evidenceStore_id IN VARCHAR2,
382 P_USER_NAME IN VARCHAR2,
383 P_USER_RESPONSE IN VARCHAR2,
384 P_ORIGINAL_RECIPIENT IN VARCHAR2 DEFAULT NULL,
385 P_OVERRIDING_COMMENT IN VARCHAR2 DEFAULT NULL,
386 x_signature_id OUT NOCOPY NUMBER );
387
388
389
390 -- ----------------------------------------
391 -- API name : Post_SignatureParameter (Bug 3447098)
392 -- Type : Public
393 -- Function : Update a document
394 -- Versions : 1.0 17-Jul-03 created
395 -- ---------------------------------------
396
400 p_commit IN VARCHAR2 default NULL,
397 PROCEDURE Post_SignatureParameters (
398 p_api_version IN NUMBER,
399 p_init_msg_list IN VARCHAR2 default NULL,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2,
404 p_signature_id IN NUMBER,
405 p_sig_parameters_tbl IN qa_edr_standard.Params_tbl_type );
406
407 end qa_edr_standard;