1 PACKAGE BODY EDR_MSCA_UTIL AS
2 /* $Header: EDRVMTLB.pls 120.1.12000000.1 2007/01/18 05:56:31 appldev ship $ */
3
4 --This procedure obtains the e-record text for a given e-record ID.
5 PROCEDURE GET_ERECORD_TEXT(p_erecord_id IN NUMBER,
6 x_text_erecord OUT NOCOPY VARCHAR2,
7 x_error_msg OUT NOCOPY VARCHAR2)
8 IS
9 l_document EDR_PSIG.DOCUMENT;
10 l_docparams EDR_PSIG.params_table;
11 l_signatures EDR_PSIG.SignatureTable;
12 l_error_num NUMBER := null;
13 EDR_PSIG_DOCERR EXCEPTION;
14
15 BEGIN
16 --Obtain the document details from the evidence for the specified e-record ID.
17 EDR_PSIG.getDocumentDetails(P_DOCUMENT_ID => p_erecord_id,
18 P_DOCUMENT => l_document,
19 P_DOCPARAMS => l_docparams,
20 P_SIGNATURES => l_signatures,
21 P_ERROR => l_error_num,
22 P_ERROR_MSG => x_error_msg);
23
24 --Raise an error based on the value of l_error_num.
25 if(l_error_num is not null) then
26 raise EDR_PSIG_DOCERR;
27 end if;
28
29 --We would be performing the following operations.
30 --If e-record is of type "text" then we attempt to read only 32K of data.
31 --If e-record of any other type, we set the e-record text to a blank value.
32 if(l_DOCUMENT.PSIG_DOCUMENTFORMAT = 'TEXT' OR l_DOCUMENT.PSIG_DOCUMENTFORMAT = 'text/plain') then
33 x_text_erecord := DBMS_LOB.SUBSTR(l_DOCUMENT.PSIG_DOCUMENT, EDR_CONSTANTS_PUB.G_MAX_INT, 1);
34 else
35 x_text_erecord := ' ';
36 end if;
37
38 EXCEPTION
39 WHEN OTHERS THEN
40 x_text_erecord := SQLERRM;
41 raise;
42 END GET_ERECORD_TEXT;
43
44
45 --This method is used to obtain the lookup details for the specified lookup.
46 PROCEDURE GET_LOOKUP(x_lookup OUT NOCOPY l_genref,
47 p_lookup_type IN VARCHAR2,
48 p_meaning IN VARCHAR2)
49 IS
50
51 BEGIN
52
53 --Obtain the lookup details.
54 OPEN x_lookup FOR
55 select MEANING, LOOKUP_CODE
56 FROM FND_LOOKUP_VALUES_VL
57 WHERE LOOKUP_TYPE = p_lookup_type
58 AND UPPER(MEANING) LIKE UPPER(p_meaning)||'%';
59
60 END GET_LOOKUP;
61
62 --This procedure obtains the lookup details except those involving the excluded code.
63 PROCEDURE GET_LOOKUP(x_lookup OUT NOCOPY l_genref,
64 p_lookup_type IN VARCHAR2,
65 p_exclude_lookup_code IN VARCHAR2,
66 p_meaning IN VARCHAR2)
67 IS
68
69 BEGIN
70
71 --Obtain the required lookup details.
72 OPEN x_lookup FOR
73 select MEANING, LOOKUP_CODE
74 FROM FND_LOOKUP_VALUES_VL
75 WHERE LOOKUP_TYPE = p_lookup_type
76 AND LOOKUP_CODE <> p_exclude_lookup_code
77 AND UPPER(MEANING) LIKE UPPER(p_meaning)||'%';
78
79 END GET_LOOKUP;
80
81
82 --This API obtains the approvers for a given event ID.
83 PROCEDURE GET_APPROVERS(x_approvers OUT NOCOPY l_genref,
84 p_eventkey IN NUMBER,
85 p_approverName IN VARCHAR2)
86
87 IS
88
89 BEGIN
90 --Obtain the approver list.
91 OPEN x_approvers FOR
92 Select EDR_UTILITIES.GETUSERDISPLAYNAME(ESIG.USER_NAME) USER_DISPLAY_NAME,
93 ESIG.SIGNATURE_SEQUENCE,
94 ESIG.USER_NAME
95 FROM EDR_ESIGNATURES ESIG,EDR_ERECORDS EREC
96 where EREC.EVENT_ID=ESIG.EVENT_ID
97 and ESIG.EVENT_ID = p_eventkey
98 and ESIG.SIGNATURE_STATUS = 'PENDING'
99 and UPPER(EDR_UTILITIES.GETUSERDISPLAYNAME(ESIG.USER_NAME)) like UPPER(p_approverName) || '%'
100 order by SIGNATURE_SEQUENCE;
101
102 END GET_APPROVERS;
103
104 --This procedure obtains all the forms based test scenario details
105 PROCEDURE GET_TEST_SCENARIO_DETAILS( x_test_scenario_details OUT NOCOPY l_genref)
106
107 IS
108
109 BEGIN
110
111 --Obtain the required test scenario details.
112 OPEN x_test_scenario_details for
113 SELECT TEST_SCENARIO,
114 TEST_SCENARIO_INSTANCE,
115 TEST_SCENARIO_ID
116
117 FROM EDR_INTER_EVENT_TEST_SCENARIOS
118 WHERE TEST_SCENARIO_TYPE is null or TEST_SCENARIO_TYPE = 'F'
119 ORDER BY TEST_SCENARIO_ID DESC;
120 END GET_TEST_SCENARIO_DETAILS;
121
122 END EDR_MSCA_UTIL;
[Home] [Help]