1 PACKAGE BODY AD_UPDATE_WORK_PARAMS_PKG AS
2 -- $Header: adupwrkb.pls 115.3 2004/09/17 07:37:07 msailoz ship $
3
4 PROCEDURE SET_PARAMETER(
5 p_owner IN VARCHAR2,
6 p_name IN VARCHAR2,
7 p_value IN VARCHAR2)
8 IS
9 l_owner AD_UPDATE_WORK_PARAMS.owner%TYPE ;
10 l_name AD_UPDATE_WORK_PARAMS.name%TYPE ;
11 BEGIN
12 l_owner := upper(p_owner);
13 l_name := upper(p_name);
14 --Update the value if the parameter already exists
15 UPDATE AD_UPDATE_WORK_PARAMS
16 SET value = p_value,
17 last_update_date = SYSDATE,
18 last_updated_by = SYSADMIN_VALUE
19 WHERE owner = l_owner
20 AND name = l_name
21 AND session_id = GLOBAL_SESSION_VALUE;
22
23 IF SQL%ROWCOUNT = 0 THEN
24 --Create a new parameter if the parameter has not been updated
25 INSERT INTO AD_UPDATE_WORK_PARAMS(
26 session_id,
27 owner,
28 name,
29 value,
30 creation_date,
31 created_by,
32 last_update_date,
33 last_updated_by)
34 VALUES(
35 GLOBAL_SESSION_VALUE,
36 l_owner,
37 l_name,
38 p_value,
39 SYSDATE ,
40 SYSADMIN_VALUE,
41 SYSDATE ,
42 SYSADMIN_VALUE);
43
44 END IF ;
45
46 COMMIT ;
47
48 END SET_PARAMETER;
49
50 FUNCTION GET_PARAMETER(
51 p_owner IN VARCHAR2,
52 p_name IN VARCHAR2)
53 RETURN VARCHAR2
54 IS
55 l_value AD_UPDATE_WORK_PARAMS.value%TYPE ;
56 BEGIN
57 --Get the value for the parameter which is global
58 SELECT value INTO l_value
59 FROM AD_UPDATE_WORK_PARAMS
60 WHERE session_id = GLOBAL_SESSION_VALUE
61 AND owner = upper(p_owner)
62 AND name = upper(p_name);
63
64 RETURN l_value;
65
66 EXCEPTION
67 --If the value doesnot exist return undefined value
68 WHEN NO_DATA_FOUND THEN
69 RETURN UNDEF_VALUE;
70 END GET_PARAMETER;
71
72 PROCEDURE SET_SESSION_PARAMETER(
73 p_session_id IN NUMBER,
74 p_owner IN VARCHAR2,
75 p_name IN VARCHAR2,
76 p_value IN VARCHAR2)
77 IS
78 l_owner AD_UPDATE_WORK_PARAMS.owner%TYPE ;
79 l_name AD_UPDATE_WORK_PARAMS.name%TYPE ;
80 BEGIN
81
82 l_owner := upper(p_owner);
83 l_name := upper(p_name);
84 --Update the value if the parameter already exists for the session
85 UPDATE AD_UPDATE_WORK_PARAMS
86 SET value = p_value,
87 last_update_date = SYSDATE,
88 last_updated_by = SYSADMIN_VALUE
89 WHERE owner = l_owner
90 AND name = l_name
91 AND session_id = p_session_id;
92
93 IF SQL%ROWCOUNT = 0 THEN
94 --Create a new parameter if the parameter has not been updated for the session
95 INSERT INTO AD_UPDATE_WORK_PARAMS(
96 session_id,
97 owner,
98 name,
99 value,
100 creation_date,
101 created_by,
102 last_update_date,
103 last_updated_by)
104 VALUES(
105 p_session_id,
106 l_owner,
107 l_name,
108 p_value,
109 SYSDATE ,
110 SYSADMIN_VALUE,
111 SYSDATE,
112 SYSADMIN_VALUE);
113
114 END IF ;
115
116 COMMIT ;
117
118 END SET_SESSION_PARAMETER;
119
120
121 FUNCTION GET_SESSION_PARAMETER(
122 p_session_id IN NUMBER,
123 p_owner IN VARCHAR2,
124 p_name IN VARCHAR2)
125 RETURN VARCHAR2
126 IS
127 l_value AD_UPDATE_WORK_PARAMS.value%TYPE ;
128 BEGIN
129 --Get the parameter for the name, owner and session
130 SELECT value INTO l_value
131 FROM AD_UPDATE_WORK_PARAMS
132 WHERE session_id = p_session_id
133 AND owner = upper(p_owner)
134 AND name = upper(p_name);
135
136 RETURN l_value;
137
138 EXCEPTION
139 --Return undefined if the parameter has not been defined
140 WHEN NO_DATA_FOUND THEN
141 RETURN UNDEF_VALUE;
142
143 END GET_SESSION_PARAMETER;
144
145 END AD_UPDATE_WORK_PARAMS_PKG;
[Home] [Help]