1 PACKAGE edr_standard AS
2 /* $Header: EDRSTNDS.pls 120.3.12000000.1 2007/01/18 05:55:38 appldev ship $
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 Type RuleInputvalues is record(
18 input_name varchar2(240),
19 input_value varchar2(240)
20 );
21 Type ameRuleinputvalues is table of ruleInputvalues index by binary_integer;
22
23
24
25 /* signature Status. This Procedure returns signature status for a given event.
26 The status is for the latest event happened and has values 'PENDING','COMPLETE','ERROR' */
27 PROCEDURE PSIG_STATUS
28 (
29 p_event in varchar2,
30 p_event_key in varchar2,
31 P_status out NOCOPY varchar2
32 ) ;
33
34 /* signature Requirement. This Procedure returns signature requireemnt for a given event.
35 The status is boolean ('TRUE','FALSE') */
36
37 PROCEDURE PSIG_REQUIRED
38 (
39 p_event in varchar2,
40 p_event_key in varchar2,
41 P_status out NOCOPY boolean
42 ) ;
43
44 /* eRecord Requirement. This Procedure returns eRecord Requirement for a given event.
45 The status is boolean ('TRUE','FALSE') */
46
47 PROCEDURE EREC_REQUIRED
48 (
49 p_event in varchar2,
50 p_event_key in varchar2,
51 P_status out NOCOPY boolean
52 ) ;
53
54 /* This function will be called from forms before calling the Transaction Query form */
55
56 FUNCTION PSIG_QUERY(p_eventQuery EDR_STANDARD.eventQuery) return number;
57
58 /* This procedure will be called to get AME input variable for a given transaction*/
59
60 -- Bug 3214495 : Start
61
62 /* This is deprecated API. plz use GET_AMERULE_INPUT_VARIABLES */
63
64 PROCEDURE GET_AMERULE_INPUT_VALUES(ameapplication IN varchar2,
65 ameruleid IN NUMBER,
66 amerulename IN VARCHAR2,
67 ameruleinputvalues OUT NOCOPY EDR_STANDARD.ameruleinputvalues) ;
68
69
70 /* This is the procedure that will take Transaction id as input instead of application Name */
71
72 PROCEDURE GET_AMERULE_INPUT_VARIABLES(transactiontypeid IN VARCHAR2,
73 ameruleid IN NUMBER,
74 amerulename IN VARCHAR2,
75 ameruleinputvalues OUT NOCOPY EDR_STANDARD.ameruleinputvalues) ;
76
77
78 -- Bug 3214495 : End
79
80 -- --------------------------------------
81 -- API name : DISPLAY_DATE
82 -- Type : Public
83 -- Pre-reqs : None
84 -- Function : convert a date to string
85 -- Parameters
86 -- IN : p_date_in DATE date to be displayed
87 -- OUT : p_date_out VARCHAR2 date in server timezone in display date format
88 -- Versions : 1.0
89 -- ---------------------------------------
90 /* This Funcitons Returns a display date format */
91 PROCEDURE DISPLAY_DATE(P_DATE_IN IN DATE ,
92 P_DATE_OUT OUT NOCOPY Varchar2) ;
93
94
95 -- --------------------------------------
96 -- API name : DISPLAY_DATE_ONLY
97 -- Type : Public
98 -- Pre-reqs : None
99 -- Function : convert a date to string
100 -- Parameters
101 -- IN : p_date_in DATE date to be displayed
102 -- OUT : p_date_out VARCHAR2 date in server timezone in display date format
103 -- Versions : 1.0 7-Nov-03
104 -- ---------------------------------------
105 /* This Funcitons Returns a display date format only */
106 PROCEDURE DISPLAY_DATE_ONLY(P_DATE_IN IN DATE ,
107 P_DATE_OUT OUT NOCOPY Varchar2) ;
108
109 -- --------------------------------------
110 -- API name : DISPLAY_TIME_ONLY
111 -- Type : Public
112 -- Pre-reqs : None
113 -- Function : convert a date to string
114 -- Parameters
115 -- IN : p_date_in DATE date to be displayed
116 -- OUT : p_date_out VARCHAR2 time in server timezone in display date format
117 -- Versions : 1.0 7-Nov-03
118 -- ---------------------------------------
119 /* This Funcitons Returns a display date format only */
120 PROCEDURE DISPLAY_TIME_ONLY(P_DATE_IN IN DATE ,
121 P_DATE_OUT OUT NOCOPY Varchar2) ;
122
123
124 /* This function will compare audit values and return true or false based on the the results */
125 FUNCTION COMPARE_AUDITVALUES(P_TABLE_NAME IN VARCHAR2,
126 P_COLUMN IN VARCHAR2,
127 P_PKNAME IN VARCHAR2,
128 P_PKVALUE IN VARCHAR2
129 )
130 return varchar2;
131 PROCEDURE FIND_WF_NTF_RECIPIENT(P_ORIGINAL_RECIPIENT IN VARCHAR2,
132 P_MESSAGE_TYPE IN VARCHAR2,
133 P_MESSAGE_NAME IN VARCHAR2,
134 P_RECIPIENT IN OUT NOCOPY VARCHAR2,
135 P_NTF_ROUTING_COMMENTS IN OUT NOCOPY VARCHAR2,
136 P_ERR_CODE OUT NOCOPY varchar2,
137 P_ERR_MSG OUT NOCOPY varchar2);
138
139 /*******************************************************************************
140 ***** This procedure returns single Descriptive flex field prompt ****
141 ***** It accepts ****
142 ***** Application ID -- Descriptive Flexed Field owner Application ****
143 ***** DESC_FLEX_DEF_NAME -- Name of the Flex Definition ****
144 ***** DESC_FLEX_CONTEXT -- Flex Definition context(ATTRIBUTE_CATEGORY) ****
145 ***** COLUMN_NAME -- Attribute Column Name (ATTRIBUTE1 ...) ****
146 ***** PROMPT_TYPE -- Allowed Values LEFT OR ABOVE based on ****
147 ***** prompt type we return value from one of the ****
148 ***** following field ****
149 ***** LEFT --> FORM_LEFT_PROMPT ****
150 ***** ABOVE --> FORM_ABOVE_PROMPT ****
151 ***** COLUMN_PROMPT -- Returns Prompt for Column Name passed ****
152 ********************************************************************************/
153
154 PROCEDURE GET_DESC_FLEX_SINGLE_PROMPT(P_APPLICATION_ID IN NUMBER,
155 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
156 P_DESC_FLEX_CONTEXT IN VARCHAR2,
157 P_COLUMN_NAME IN VARCHAR2,
158 P_PROMPT_TYPE IN VARCHAR2 DEFAULT 'LEFT',
159 P_COLUMN_PROMPT OUT NOCOPY VARCHAR2);
160
161
162 /*******************************************************************************
163 ***** This procedure returns all 30 Descriptive flex field prompt ****
164 ***** It accepts ****
165 ***** Application ID -- Descriptive Flexed Field owner Application ****
166 ***** DESC_FLEX_DEF_NAME -- Name of the Flex Definition ****
167 ***** DESC_FLEX_CONTEXT -- Flex Definition context(ATTRIBUTE_CATEGORY) ****
168 ***** PROMPT_TYPE -- Allowed Values LEFT OR ABOVE based on ****
169 ***** prompt type we return value from one of the ****
170 ***** following field ****
171 ***** LEFT --> FORM_LEFT_PROMPT ****
172 ***** ABOVE --> FORM_ABOVE_PROMPT ****
173 ***** COLUMN1_PROMPT -- Returns Prompt for Column Name passed ****
174 ***** ....... COLUMN30_PROMPT ****
175 ********************************************************************************/
176
177 PROCEDURE GET_DESC_FLEX_ALL_PROMPTS(P_APPLICATION_ID IN NUMBER,
178 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
179 P_DESC_FLEX_CONTEXT IN VARCHAR2,
180 P_PROMPT_TYPE IN VARCHAR2 DEFAULT 'LEFT',
181 P_COLUMN1_NAME IN VARCHAR2 DEFAULT NULL,
182 P_COLUMN2_NAME IN VARCHAR2 DEFAULT NULL,
183 P_COLUMN3_NAME IN VARCHAR2 DEFAULT NULL,
184 P_COLUMN4_NAME IN VARCHAR2 DEFAULT NULL,
185 P_COLUMN5_NAME IN VARCHAR2 DEFAULT NULL,
186 P_COLUMN6_NAME IN VARCHAR2 DEFAULT NULL,
187 P_COLUMN7_NAME IN VARCHAR2 DEFAULT NULL,
188 P_COLUMN8_NAME IN VARCHAR2 DEFAULT NULL,
189 P_COLUMN9_NAME IN VARCHAR2 DEFAULT NULL,
190 P_COLUMN10_NAME IN VARCHAR2 DEFAULT NULL,
191 P_COLUMN11_NAME IN VARCHAR2 DEFAULT NULL,
192 P_COLUMN12_NAME IN VARCHAR2 DEFAULT NULL,
193 P_COLUMN13_NAME IN VARCHAR2 DEFAULT NULL,
194 P_COLUMN14_NAME IN VARCHAR2 DEFAULT NULL,
195 P_COLUMN15_NAME IN VARCHAR2 DEFAULT NULL,
196 P_COLUMN16_NAME IN VARCHAR2 DEFAULT NULL,
197 P_COLUMN17_NAME IN VARCHAR2 DEFAULT NULL,
198 P_COLUMN18_NAME IN VARCHAR2 DEFAULT NULL,
199 P_COLUMN19_NAME IN VARCHAR2 DEFAULT NULL,
200 P_COLUMN20_NAME IN VARCHAR2 DEFAULT NULL,
201 P_COLUMN21_NAME IN VARCHAR2 DEFAULT NULL,
202 P_COLUMN22_NAME IN VARCHAR2 DEFAULT NULL,
203 P_COLUMN23_NAME IN VARCHAR2 DEFAULT NULL,
204 P_COLUMN24_NAME IN VARCHAR2 DEFAULT NULL,
205 P_COLUMN25_NAME IN VARCHAR2 DEFAULT NULL,
206 P_COLUMN26_NAME IN VARCHAR2 DEFAULT NULL,
207 P_COLUMN27_NAME IN VARCHAR2 DEFAULT NULL,
208 P_COLUMN28_NAME IN VARCHAR2 DEFAULT NULL,
209 P_COLUMN29_NAME IN VARCHAR2 DEFAULT NULL,
210 P_COLUMN30_NAME IN VARCHAR2 DEFAULT NULL,
211 P_COLUMN1_PROMPT OUT NOCOPY VARCHAR2,
212 P_COLUMN2_PROMPT OUT NOCOPY VARCHAR2,
213 P_COLUMN3_PROMPT OUT NOCOPY VARCHAR2,
214 P_COLUMN4_PROMPT OUT NOCOPY VARCHAR2,
215 P_COLUMN5_PROMPT OUT NOCOPY VARCHAR2,
216 P_COLUMN6_PROMPT OUT NOCOPY VARCHAR2,
217 P_COLUMN7_PROMPT OUT NOCOPY VARCHAR2,
218 P_COLUMN8_PROMPT OUT NOCOPY VARCHAR2,
219 P_COLUMN9_PROMPT OUT NOCOPY VARCHAR2,
220 P_COLUMN10_PROMPT OUT NOCOPY VARCHAR2,
221 P_COLUMN11_PROMPT OUT NOCOPY VARCHAR2,
222 P_COLUMN12_PROMPT OUT NOCOPY VARCHAR2,
223 P_COLUMN13_PROMPT OUT NOCOPY VARCHAR2,
224 P_COLUMN14_PROMPT OUT NOCOPY VARCHAR2,
225 P_COLUMN15_PROMPT OUT NOCOPY VARCHAR2,
226 P_COLUMN16_PROMPT OUT NOCOPY VARCHAR2,
227 P_COLUMN17_PROMPT OUT NOCOPY VARCHAR2,
228 P_COLUMN18_PROMPT OUT NOCOPY VARCHAR2,
229 P_COLUMN19_PROMPT OUT NOCOPY VARCHAR2,
230 P_COLUMN20_PROMPT OUT NOCOPY VARCHAR2,
231 P_COLUMN21_PROMPT OUT NOCOPY VARCHAR2,
232 P_COLUMN22_PROMPT OUT NOCOPY VARCHAR2,
233 P_COLUMN23_PROMPT OUT NOCOPY VARCHAR2,
234 P_COLUMN24_PROMPT OUT NOCOPY VARCHAR2,
235 P_COLUMN25_PROMPT OUT NOCOPY VARCHAR2,
236 P_COLUMN26_PROMPT OUT NOCOPY VARCHAR2,
237 P_COLUMN27_PROMPT OUT NOCOPY VARCHAR2,
238 P_COLUMN28_PROMPT OUT NOCOPY VARCHAR2,
239 P_COLUMN29_PROMPT OUT NOCOPY VARCHAR2,
240 P_COLUMN30_PROMPT OUT NOCOPY VARCHAR2);
241
242 --Bug 4501520 :rvsingh: start
243 /*******************************************************************************
244 ***** This procedure returns all 30 Descriptive flex field values ****
245 ***** It accepts ****
246 ***** Application ID -- Descriptive Flexed Field owner Application ****
247 ***** DESC_FLEX_DEF_NAME -- Name of the Flex Definition ****
248 ***** DESC_FLEX_CONTEXT -- Flex Definition context(ATTRIBUTE_CATEGORY) ****
249 ***** P_COLUMN1_NAME ..P_COLUMN30_NAME -- Name of the columns ****
250 ***** P_COLUMN1_ID_VAL ..P_COLUMN30_ID_VAL -- ID or values of columns passed*
251 ***** if the id is passed then corresponding value****
252 ***** else ****
253 ***** value is returned ****
254 ***** COLUMN1_VAL -- Returns value for Column Name passed ****
255 ***** ....... COLUMN30_VAL ****
256 ********************************************************************************/
257
258 PROCEDURE GET_DESC_FLEX_ALL_VALUES(P_APPLICATION_ID IN NUMBER,
259 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
260 P_DESC_FLEX_CONTEXT IN VARCHAR2,
261 P_COLUMN1_NAME IN VARCHAR2 DEFAULT NULL,
262 P_COLUMN2_NAME IN VARCHAR2 DEFAULT NULL,
263 P_COLUMN3_NAME IN VARCHAR2 DEFAULT NULL,
264 P_COLUMN4_NAME IN VARCHAR2 DEFAULT NULL,
265 P_COLUMN5_NAME IN VARCHAR2 DEFAULT NULL,
266 P_COLUMN6_NAME IN VARCHAR2 DEFAULT NULL,
267 P_COLUMN7_NAME IN VARCHAR2 DEFAULT NULL,
268 P_COLUMN8_NAME IN VARCHAR2 DEFAULT NULL,
269 P_COLUMN9_NAME IN VARCHAR2 DEFAULT NULL,
270 P_COLUMN10_NAME IN VARCHAR2 DEFAULT NULL,
271 P_COLUMN11_NAME IN VARCHAR2 DEFAULT NULL,
272 P_COLUMN12_NAME IN VARCHAR2 DEFAULT NULL,
273 P_COLUMN13_NAME IN VARCHAR2 DEFAULT NULL,
274 P_COLUMN14_NAME IN VARCHAR2 DEFAULT NULL,
275 P_COLUMN15_NAME IN VARCHAR2 DEFAULT NULL,
276 P_COLUMN16_NAME IN VARCHAR2 DEFAULT NULL,
277 P_COLUMN17_NAME IN VARCHAR2 DEFAULT NULL,
278 P_COLUMN18_NAME IN VARCHAR2 DEFAULT NULL,
279 P_COLUMN19_NAME IN VARCHAR2 DEFAULT NULL,
280 P_COLUMN20_NAME IN VARCHAR2 DEFAULT NULL,
281 P_COLUMN21_NAME IN VARCHAR2 DEFAULT NULL,
282 P_COLUMN22_NAME IN VARCHAR2 DEFAULT NULL,
283 P_COLUMN23_NAME IN VARCHAR2 DEFAULT NULL,
284 P_COLUMN24_NAME IN VARCHAR2 DEFAULT NULL,
288 P_COLUMN28_NAME IN VARCHAR2 DEFAULT NULL,
285 P_COLUMN25_NAME IN VARCHAR2 DEFAULT NULL,
286 P_COLUMN26_NAME IN VARCHAR2 DEFAULT NULL,
287 P_COLUMN27_NAME IN VARCHAR2 DEFAULT NULL,
289 P_COLUMN29_NAME IN VARCHAR2 DEFAULT NULL,
290 P_COLUMN30_NAME IN VARCHAR2 DEFAULT NULL,
291 P_COLUMN1_ID_VAL IN VARCHAR2 DEFAULT NULL,
292 P_COLUMN2_ID_VAL IN VARCHAR2 DEFAULT NULL,
293 P_COLUMN3_ID_VAL IN VARCHAR2 DEFAULT NULL,
294 P_COLUMN4_ID_VAL IN VARCHAR2 DEFAULT NULL,
295 P_COLUMN5_ID_VAL IN VARCHAR2 DEFAULT NULL,
296 P_COLUMN6_ID_VAL IN VARCHAR2 DEFAULT NULL,
297 P_COLUMN7_ID_VAL IN VARCHAR2 DEFAULT NULL,
298 P_COLUMN8_ID_VAL IN VARCHAR2 DEFAULT NULL,
299 P_COLUMN9_ID_VAL IN VARCHAR2 DEFAULT NULL,
300 P_COLUMN10_ID_VAL IN VARCHAR2 DEFAULT NULL,
301 P_COLUMN11_ID_VAL IN VARCHAR2 DEFAULT NULL,
302 P_COLUMN12_ID_VAL IN VARCHAR2 DEFAULT NULL,
303 P_COLUMN13_ID_VAL IN VARCHAR2 DEFAULT NULL,
304 P_COLUMN14_ID_VAL IN VARCHAR2 DEFAULT NULL,
305 P_COLUMN15_ID_VAL IN VARCHAR2 DEFAULT NULL,
306 P_COLUMN16_ID_VAL IN VARCHAR2 DEFAULT NULL,
307 P_COLUMN17_ID_VAL IN VARCHAR2 DEFAULT NULL,
308 P_COLUMN18_ID_VAL IN VARCHAR2 DEFAULT NULL,
309 P_COLUMN19_ID_VAL IN VARCHAR2 DEFAULT NULL,
310 P_COLUMN20_ID_VAL IN VARCHAR2 DEFAULT NULL,
311 P_COLUMN21_ID_VAL IN VARCHAR2 DEFAULT NULL,
312 P_COLUMN22_ID_VAL IN VARCHAR2 DEFAULT NULL,
313 P_COLUMN23_ID_VAL IN VARCHAR2 DEFAULT NULL,
314 P_COLUMN24_ID_VAL IN VARCHAR2 DEFAULT NULL,
315 P_COLUMN25_ID_VAL IN VARCHAR2 DEFAULT NULL,
316 P_COLUMN26_ID_VAL IN VARCHAR2 DEFAULT NULL,
317 P_COLUMN27_ID_VAL IN VARCHAR2 DEFAULT NULL,
318 P_COLUMN28_ID_VAL IN VARCHAR2 DEFAULT NULL,
319 P_COLUMN29_ID_VAL IN VARCHAR2 DEFAULT NULL,
320 P_COLUMN30_ID_VAL IN VARCHAR2 DEFAULT NULL,
321 P_COLUMN1_VAL OUT NOCOPY VARCHAR2,
322 P_COLUMN2_VAL OUT NOCOPY VARCHAR2,
323 P_COLUMN3_VAL OUT NOCOPY VARCHAR2,
324 P_COLUMN4_VAL OUT NOCOPY VARCHAR2,
325 P_COLUMN5_VAL OUT NOCOPY VARCHAR2,
326 P_COLUMN6_VAL OUT NOCOPY VARCHAR2,
327 P_COLUMN7_VAL OUT NOCOPY VARCHAR2,
328 P_COLUMN8_VAL OUT NOCOPY VARCHAR2,
329 P_COLUMN9_VAL OUT NOCOPY VARCHAR2,
330 P_COLUMN10_VAL OUT NOCOPY VARCHAR2,
331 P_COLUMN11_VAL OUT NOCOPY VARCHAR2,
332 P_COLUMN12_VAL OUT NOCOPY VARCHAR2,
333 P_COLUMN13_VAL OUT NOCOPY VARCHAR2,
334 P_COLUMN14_VAL OUT NOCOPY VARCHAR2,
335 P_COLUMN15_VAL OUT NOCOPY VARCHAR2,
336 P_COLUMN16_VAL OUT NOCOPY VARCHAR2,
337 P_COLUMN17_VAL OUT NOCOPY VARCHAR2,
338 P_COLUMN18_VAL OUT NOCOPY VARCHAR2,
339 P_COLUMN19_VAL OUT NOCOPY VARCHAR2,
340 P_COLUMN20_VAL OUT NOCOPY VARCHAR2,
341 P_COLUMN21_VAL OUT NOCOPY VARCHAR2,
342 P_COLUMN22_VAL OUT NOCOPY VARCHAR2,
343 P_COLUMN23_VAL OUT NOCOPY VARCHAR2,
344 P_COLUMN24_VAL OUT NOCOPY VARCHAR2,
345 P_COLUMN25_VAL OUT NOCOPY VARCHAR2,
346 P_COLUMN26_VAL OUT NOCOPY VARCHAR2,
347 P_COLUMN27_VAL OUT NOCOPY VARCHAR2,
348 P_COLUMN28_VAL OUT NOCOPY VARCHAR2,
349 P_COLUMN29_VAL OUT NOCOPY VARCHAR2,
350 P_COLUMN30_VAL OUT NOCOPY VARCHAR2);
351
352 --Bug 4501520 :rvsingh: end
353 /*******************************************************************************
354 ***** This procedure returns Lookup code meaning ****
355 ***** It accepts ****
356 ***** LOOKUP_TYPE and LOOKUP CODE as in parameter and Returns MEANING ****
357 ***** as out parameter. This uses FND_LOOKUPS View ****
358 ********************************************************************************/
359
360 PROCEDURE GET_MEANING(P_LOOKUP_TYPE IN VARCHAR2,
361 P_LOOKUP_CODE IN VARCHAR2,
362 P_MEANING OUT NOCOPY VARCHAR2);
363
364 /*******************************************************************************
365 ***** This procedure returns USER RESPONSE ****
366 ***** It accepts ****
367 ***** LOOKUP_TYPE and PSIG_STATUS as in parameter and Returns RESPONSE ****
368 ***** as out parameter. This uses FND_LOOKUPS View ****
369 ********************************************************************************/
370 -- Bug 4865689 :start
371 PROCEDURE GET_USER_RESPONSE(P_LOOKUP_TYPE IN VARCHAR2,
372 P_PSIG_STATUS IN VARCHAR2,
373 P_RESPONSE OUT NOCOPY VARCHAR2);
374
375 -- Bug 4865689 :end
376 /**** It is a simplied version wrapped over PSIG_QUERY for Java api ***/
377
378 PROCEDURE PSIG_QUERY_ONE (p_event_name IN FND_TABLE_OF_VARCHAR2_255,
379 p_event_key IN FND_TABLE_OF_VARCHAR2_255,
380 o_query_id OUT NOCOPY NUMBER );
381 end edr_STANDARD;