[Home] [Help]
PACKAGE BODY: APPS.CSC_PLAN_ENABLE_SETUP_PKG
Source
1 PACKAGE BODY CSC_PLAN_ENABLE_SETUP_PKG as
2 /* $Header: cscteplb.pls 120.0 2005/05/30 15:52:34 appldev noship $ */
3 -- Start of Comments
4 -- Package name : CSC_PLAN_ENABLE_SETUP_PKG
5 -- Purpose : Table handler package to perform inserts, update, deletes and lock
6 -- row operations on CSC_PLAN_ENABLE_SETUP table.
7 -- History :
8 -- MM-DD-YYYY NAME MODIFICATIONS
9 -- 01-13-2000 dejoseph Created.
10 -- 01-31-2000 dejoseph 'Arcs'ed in for fourth code freeze. (07-FEB-2000)
11 -- 02-13-2000 dejoseph 'Arcs'ed on for fifth code freeze. (21-FEB-2000)
12 -- 02-28-2000 dejoseph 'Arcs'ed on for sixth code freeze. (06-MAR-2000)
13 -- 04-06-2000 dejoseph Defaulted start_date_active to sysdate if not specified.
14 -- Fix to bug # 1253012.
15 -- 11-12-2002 bhroy NOCOPY changes made
16 -- 12-03-2002 bhroy Added check-in comments, WHENEVER OSERROR EXIT FAILURE ROLLBACK
17 -- 10-05-2004 bhroy Fixed bug# 3864025, allow to enter NULL in update_row API, if user enters NULL from UI
18 -- NOTE :
19 -- End of Comments
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSC_PLAN_ENABLE_SETUP_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscteplb.pls';
24
25 PROCEDURE Insert_Row(
26 P_FUNCTION_ID IN NUMBER,
27 P_START_DATE_ACTIVE IN DATE,
28 P_END_DATE_ACTIVE IN DATE,
29 P_ON_INSERT_ENABLE_FLAG IN VARCHAR2,
30 P_ON_UPDATE_ENABLE_FLAG IN VARCHAR2,
31 P_CUSTOM1_ENABLE_FLAG IN VARCHAR2,
32 P_CUSTOM2_ENABLE_FLAG IN VARCHAR2,
33 P_CREATION_DATE IN DATE,
34 P_LAST_UPDATE_DATE IN DATE,
35 P_CREATED_BY IN NUMBER,
36 P_LAST_UPDATED_BY IN NUMBER,
37 P_LAST_UPDATE_LOGIN IN NUMBER,
38 P_ATTRIBUTE1 IN VARCHAR2 := NULL,
39 P_ATTRIBUTE2 IN VARCHAR2 := NULL,
40 P_ATTRIBUTE3 IN VARCHAR2 := NULL,
41 P_ATTRIBUTE4 IN VARCHAR2 := NULL,
42 P_ATTRIBUTE5 IN VARCHAR2 := NULL,
43 P_ATTRIBUTE6 IN VARCHAR2 := NULL,
44 P_ATTRIBUTE7 IN VARCHAR2 := NULL,
45 P_ATTRIBUTE8 IN VARCHAR2 := NULL,
46 P_ATTRIBUTE9 IN VARCHAR2 := NULL,
47 P_ATTRIBUTE10 IN VARCHAR2 := NULL,
48 P_ATTRIBUTE11 IN VARCHAR2 := NULL,
49 P_ATTRIBUTE12 IN VARCHAR2 := NULL,
50 P_ATTRIBUTE13 IN VARCHAR2 := NULL,
51 P_ATTRIBUTE14 IN VARCHAR2 := NULL,
52 P_ATTRIBUTE15 IN VARCHAR2 := NULL,
53 P_ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL,
54 X_ENABLE_SETUP_ID OUT NOCOPY NUMBER,
55 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER)
56 IS
57 CURSOR C1 IS
58 SELECT CSC_PLAN_ENABLE_SETUP_S.nextval
59 FROM sys.dual;
60
61 l_enable_setup_id NUMBER;
62
63 L_API_NAME VARCHAR2(30) := 'Insert_Row';
64 L_MSG_COUNT NUMBER;
65 L_MSG_DATA VARCHAR2(2000);
66 L_RETURN_STATUS VARCHAR2(1);
67
68 BEGIN
69 OPEN C1;
70 FETCH C1 INTO L_ENABLE_SETUP_ID;
71 CLOSE C1;
72
73 INSERT INTO CSC_PLAN_ENABLE_SETUP(
74 ENABLE_SETUP_ID, FUNCTION_ID, START_DATE_ACTIVE,
75 END_DATE_ACTIVE, ON_INSERT_ENABLE_FLAG, ON_UPDATE_ENABLE_FLAG,
76 CUSTOM1_ENABLE_FLAG, CUSTOM2_ENABLE_FLAG, CREATION_DATE,
77 LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY,
78 LAST_UPDATE_LOGIN, ATTRIBUTE1, ATTRIBUTE2,
79 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
80 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
81 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
82 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
83 ATTRIBUTE15, ATTRIBUTE_CATEGORY, OBJECT_VERSION_NUMBER )
84 VALUES (
85 l_enable_setup_id, P_FUNCTION_ID, nvl(P_START_DATE_ACTIVE,
86 SYSDATE),
87 P_END_DATE_ACTIVE, P_ON_INSERT_ENABLE_FLAG, P_ON_UPDATE_ENABLE_FLAG,
88 P_CUSTOM1_ENABLE_FLAG, P_CUSTOM2_ENABLE_FLAG, P_CREATION_DATE,
89 P_LAST_UPDATE_DATE, P_CREATED_BY, P_LAST_UPDATED_BY,
90 P_LAST_UPDATE_LOGIN, P_ATTRIBUTE1, P_ATTRIBUTE2,
91 P_ATTRIBUTE3, P_ATTRIBUTE4, P_ATTRIBUTE5,
92 P_ATTRIBUTE6, P_ATTRIBUTE7, P_ATTRIBUTE8,
93 P_ATTRIBUTE9, P_ATTRIBUTE10, P_ATTRIBUTE11,
94 P_ATTRIBUTE12, P_ATTRIBUTE13, P_ATTRIBUTE14,
95 P_ATTRIBUTE15, P_ATTRIBUTE_CATEGORY, 1 );
96
97 x_enable_setup_id := l_enable_setup_id;
98 x_object_version_number := 1;
99 EXCEPTION
100 WHEN OTHERS THEN
101 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
102 P_API_NAME => L_API_NAME,
103 P_PKG_NAME => G_PKG_NAME,
104 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
105 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
106 X_MSG_COUNT => L_MSG_COUNT,
107 X_MSG_DATA => L_MSG_DATA,
108 X_RETURN_STATUS => L_RETURN_STATUS);
109 APP_EXCEPTION.RAISE_EXCEPTION;
110
111 End Insert_Row;
112
113 PROCEDURE Update_Row(
114 P_ENABLE_SETUP_ID IN NUMBER,
115 P_FUNCTION_ID IN NUMBER := NULL,
116 P_START_DATE_ACTIVE IN DATE := NULL,
117 P_END_DATE_ACTIVE IN DATE := NULL,
118 P_ON_INSERT_ENABLE_FLAG IN VARCHAR2 := NULL,
119 P_ON_UPDATE_ENABLE_FLAG IN VARCHAR2 := NULL,
120 P_CUSTOM1_ENABLE_FLAG IN VARCHAR2 := NULL,
121 P_CUSTOM2_ENABLE_FLAG IN VARCHAR2 := NULL,
122 P_LAST_UPDATE_DATE IN DATE := NULL,
123 P_LAST_UPDATED_BY IN NUMBER := NULL,
124 P_LAST_UPDATE_LOGIN IN NUMBER := NULL,
125 P_ATTRIBUTE1 IN VARCHAR2 := NULL,
126 P_ATTRIBUTE2 IN VARCHAR2 := NULL,
127 P_ATTRIBUTE3 IN VARCHAR2 := NULL,
128 P_ATTRIBUTE4 IN VARCHAR2 := NULL,
129 P_ATTRIBUTE5 IN VARCHAR2 := NULL,
130 P_ATTRIBUTE6 IN VARCHAR2 := NULL,
131 P_ATTRIBUTE7 IN VARCHAR2 := NULL,
132 P_ATTRIBUTE8 IN VARCHAR2 := NULL,
133 P_ATTRIBUTE9 IN VARCHAR2 := NULL,
134 P_ATTRIBUTE10 IN VARCHAR2 := NULL,
135 P_ATTRIBUTE11 IN VARCHAR2 := NULL,
136 P_ATTRIBUTE12 IN VARCHAR2 := NULL,
137 P_ATTRIBUTE13 IN VARCHAR2 := NULL,
138 P_ATTRIBUTE14 IN VARCHAR2 := NULL,
139 P_ATTRIBUTE15 IN VARCHAR2 := NULL,
140 P_ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL,
141 P_OBJECT_VERSION_NUMBER IN NUMBER,
142 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER)
143 IS
144 L_API_NAME VARCHAR2(30) := 'Update_Row';
145 L_MSG_COUNT NUMBER;
146 L_MSG_DATA VARCHAR2(2000);
147 L_RETURN_STATUS VARCHAR2(1);
148 BEGIN
149 UPDATE csc_plan_enable_setup
150 SET
151 FUNCTION_ID = NVL( p_function_id, FUNCTION_ID),
152 START_DATE_ACTIVE = NVL( p_start_date_active, START_DATE_ACTIVE),
153 END_DATE_ACTIVE = p_end_date_active,
154 ON_INSERT_ENABLE_FLAG = NVL( p_on_insert_enable_flag, ON_INSERT_ENABLE_FLAG),
155 ON_UPDATE_ENABLE_FLAG = NVL( p_on_update_enable_flag, ON_UPDATE_ENABLE_FLAG),
156 CUSTOM1_ENABLE_FLAG = NVL( p_custom1_enable_flag, CUSTOM1_ENABLE_FLAG),
157 CUSTOM2_ENABLE_FLAG = NVL( p_custom2_enable_flag, CUSTOM2_ENABLE_FLAG),
158 LAST_UPDATE_DATE = NVL( p_last_update_date, LAST_UPDATE_DATE),
159 LAST_UPDATED_BY = NVL( p_last_updated_by, LAST_UPDATED_BY),
160 LAST_UPDATE_LOGIN = NVL( p_last_update_login, LAST_UPDATE_LOGIN),
161 ATTRIBUTE1 = NVL( p_attribute1, ATTRIBUTE1),
162 ATTRIBUTE2 = NVL( p_attribute2, ATTRIBUTE2),
163 ATTRIBUTE3 = NVL( p_attribute3, ATTRIBUTE3),
164 ATTRIBUTE4 = NVL( p_attribute3, ATTRIBUTE4),
165 ATTRIBUTE5 = NVL( p_attribute3, ATTRIBUTE5),
166 ATTRIBUTE6 = NVL( p_attribute3, ATTRIBUTE6),
167 ATTRIBUTE7 = NVL( p_attribute3, ATTRIBUTE7),
168 ATTRIBUTE8 = NVL( p_attribute3, ATTRIBUTE8),
169 ATTRIBUTE9 = NVL( p_attribute3, ATTRIBUTE9),
170 ATTRIBUTE10 = NVL( p_attribute3, ATTRIBUTE10),
171 ATTRIBUTE11 = NVL( p_attribute3, ATTRIBUTE11),
172 ATTRIBUTE12 = NVL( p_attribute3, ATTRIBUTE12),
173 ATTRIBUTE13 = NVL( p_attribute3, ATTRIBUTE13),
174 ATTRIBUTE14 = NVL( p_attribute3, ATTRIBUTE14),
175 ATTRIBUTE15 = NVL( p_attribute3, ATTRIBUTE15),
176 ATTRIBUTE_CATEGORY = NVL( p_attribute_category, ATTRIBUTE_CATEGORY),
177 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
178 WHERE ENABLE_SETUP_ID = p_enable_setup_id
179 AND OBJECT_VERSION_NUMBER = p_object_version_number
180 RETURNING OBJECT_VERSION_NUMBER INTO X_OBJECT_VERSION_NUMBER;
181
182 If (SQL%NOTFOUND) then
183 RAISE NO_DATA_FOUND;
184 End If;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
189 P_API_NAME => L_API_NAME,
190 P_PKG_NAME => G_PKG_NAME,
191 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
192 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
193 X_MSG_COUNT => L_MSG_COUNT,
194 X_MSG_DATA => L_MSG_DATA,
195 X_RETURN_STATUS => L_RETURN_STATUS);
196 APP_EXCEPTION.RAISE_EXCEPTION;
197
198 END Update_Row;
199
200 PROCEDURE Delete_Row(
201 P_ENABLE_SETUP_ID IN NUMBER,
202 P_OBJECT_VERSION_NUMBER IN NUMBER)
203 IS
204 L_API_NAME VARCHAR2(30) := 'Delete_Row';
205 L_MSG_COUNT NUMBER;
206 L_MSG_DATA VARCHAR2(2000);
207 L_RETURN_STATUS VARCHAR2(1);
208 BEGIN
209 DELETE FROM CSC_PLAN_ENABLE_SETUP
210 WHERE ENABLE_SETUP_ID = p_enable_setup_id
211 AND OBJECT_VERSION_NUMBER = p_object_version_number;
212
213 If (SQL%NOTFOUND) then
214 RAISE NO_DATA_FOUND;
215 End If;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
220 P_API_NAME => L_API_NAME,
221 P_PKG_NAME => G_PKG_NAME,
222 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
223 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
224 X_MSG_COUNT => L_MSG_COUNT,
225 X_MSG_DATA => L_MSG_DATA,
226 X_RETURN_STATUS => L_RETURN_STATUS);
227 APP_EXCEPTION.RAISE_EXCEPTION;
228
229 END Delete_Row;
230
231
232 PROCEDURE Lock_Row(
233 P_ENABLE_SETUP_ID IN NUMBER,
234 P_OBJECT_VERSION_NUMBER IN NUMBER)
235 IS
236 CURSOR C1 IS
237 SELECT *
238 FROM CSC_PLAN_ENABLE_SETUP
239 WHERE ENABLE_SETUP_ID = p_enable_setup_id
240 AND OBJECT_VERSION_NUMBER = p_object_version_number
241 FOR UPDATE NOWAIT;
242
243 Recinfo C1%ROWTYPE;
244
245 L_API_NAME VARCHAR2(30) := 'Delete_Row';
246 L_MSG_COUNT NUMBER;
247 L_MSG_DATA VARCHAR2(2000);
248 L_RETURN_STATUS VARCHAR2(1);
249 BEGIN
250 OPEN C1;
251 FETCH C1 INTO Recinfo;
252 If (C1%NOTFOUND) then
253 CLOSE C1;
254 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
255 APP_EXCEPTION.RAISE_EXCEPTION;
256 End If;
257 CLOSE C1;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 CSC_CORE_UTILS_PVT.HANDLE_EXCEPTIONS(
262 P_API_NAME => L_API_NAME,
263 P_PKG_NAME => G_PKG_NAME,
264 P_EXCEPTION_LEVEL => CSC_CORE_UTILS_PVT.G_MSG_LVL_OTHERS,
265 P_PACKAGE_TYPE => CSC_CORE_UTILS_PVT.G_PVT,
266 X_MSG_COUNT => L_MSG_COUNT,
267 X_MSG_DATA => L_MSG_DATA,
268 X_RETURN_STATUS => L_RETURN_STATUS);
269 APP_EXCEPTION.RAISE_EXCEPTION;
270
271 END Lock_Row;
272
273 End CSC_PLAN_ENABLE_SETUP_PKG;