[Home] [Help]
PACKAGE BODY: APPS.CSC_PROF_MODULE_GROUPS_PKG
Source
1 PACKAGE BODY CSC_PROF_MODULE_GROUPS_PKG as
2 /* $Header: csctpmgb.pls 120.3 2005/09/18 23:38:39 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROF_MODULE_GROUPS_PKG
5 -- Purpose :
6 -- History :
7 -- 03 Nov 00 axsubram Added load_row for NLS (# 1487333)
8 -- 26 Nov 02 JAmose Addition of NOCOPY and the Removal of Fnd_Api.G_MISS*
9 -- from the definition for the performance reason
10 -- 19 july 2005 tpalaniv Modified the logic in load_row API to fetch last_updated_by based on FND API as part
11 -- of R12 ATG Project - Seed Data Versioning
12 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
13 -- NOTE :
14 -- End of Comments
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROF_MODULE_GROUPS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctpmgb.pls';
19
20 PROCEDURE Insert_Row(
21 px_MODULE_GROUP_ID IN OUT NOCOPY NUMBER,
22 p_FORM_FUNCTION_ID NUMBER,
23 p_FORM_FUNCTION_NAME VARCHAR2,
24 p_RESPONSIBILITY_ID NUMBER,
25 p_RESP_APPL_ID NUMBER,
26 p_PARTY_TYPE VARCHAR2,
27 p_GROUP_ID NUMBER,
28 p_DASHBOARD_GROUP_FLAG VARCHAR2,
29 p_CURRENCY_CODE VARCHAR2,
30 p_LAST_UPDATE_DATE DATE,
31 p_LAST_UPDATED_BY NUMBER,
32 p_CREATION_DATE DATE,
33 p_CREATED_BY NUMBER,
34 p_LAST_UPDATE_LOGIN NUMBER,
35 p_SEEDED_FLAG VARCHAR2,
36 p_APPLICATION_ID NUMBER,
37 p_DASHBOARD_GROUP_ID NUMBER)
38
39 IS
40 CURSOR C2 IS SELECT CSC_PROF_MODULE_GROUPS_S.nextval FROM sys.dual;
41 ps_SEEDED_FLAG Varchar2(3);
42
43 BEGIN
44
45 /* added the below 2 lines for bug 4596220 */
46 ps_seeded_flag := p_seeded_flag;
47 IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
48
49 /* Added This If Condition for Bug 1944040*/
50 If p_Created_by=1 then
51 ps_seeded_flag:='Y';
52 Else
53 ps_seeded_flag:='N';
54 End If;
55 END IF;
56
57 If (px_MODULE_GROUP_ID IS NULL) OR (px_MODULE_GROUP_ID = FND_API.G_MISS_NUM) then
58 OPEN C2;
59 FETCH C2 INTO px_MODULE_GROUP_ID;
60 CLOSE C2;
61 End If;
62 INSERT INTO CSC_PROF_MODULE_GROUPS(
63 MODULE_GROUP_ID,
64 FORM_FUNCTION_ID,
65 FORM_FUNCTION_NAME,
66 RESPONSIBILITY_ID,
67 RESP_APPL_ID,
68 PARTY_TYPE,
69 GROUP_ID,
70 DASHBOARD_GROUP_FLAG,
71 CURRENCY_CODE,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 CREATION_DATE,
75 CREATED_BY,
76 LAST_UPDATE_LOGIN,
77 SEEDED_FLAG,
78 APPLICATION_ID,
79 DASHBOARD_GROUP_ID
80 ) VALUES (
81 px_MODULE_GROUP_ID,
82 decode( p_FORM_FUNCTION_ID, FND_API.G_MISS_NUM, NULL, p_FORM_FUNCTION_ID),
83 decode( p_FORM_FUNCTION_NAME,FND_API.G_MISS_CHAR,NULL, p_FORM_FUNCTION_NAME),
84 decode( p_RESPONSIBILITY_ID, FND_API.G_MISS_NUM, NULL, p_RESPONSIBILITY_ID),
85 decode( p_RESP_APPL_ID, FND_API.G_MISS_NUM, NULL, p_RESP_APPL_ID),
86 decode( p_PARTY_TYPE, FND_API.G_MISS_CHAR, NULL, p_PARTY_TYPE),
87 decode( p_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_GROUP_ID),
88 decode( p_DASHBOARD_GROUP_FLAG, FND_API.G_MISS_CHAR, NULL, p_DASHBOARD_GROUP_FLAG),
89 decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE),
90 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
91 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
92 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
93 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
94 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
95 decode( p_SEEDED_FLAG,CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, ps_SEEDED_FLAG),
96 decode( p_APPLICATION_ID,CSC_CORE_UTILS_PVT.G_MISS_NUM, NULL, p_APPLICATION_ID),
97 decode( p_DASHBOARD_GROUP_ID,CSC_CORE_UTILS_PVT.G_MISS_NUM,NULL, p_DASHBOARD_GROUP_ID));
98 End Insert_Row;
99
100 PROCEDURE Update_Row(
101 p_MODULE_GROUP_ID NUMBER,
102 p_FORM_FUNCTION_ID NUMBER,
103 p_FORM_FUNCTION_NAME VARCHAR2,
104 p_RESPONSIBILITY_ID NUMBER,
105 p_RESP_APPL_ID NUMBER,
106 p_PARTY_TYPE VARCHAR2,
107 p_GROUP_ID NUMBER,
108 p_DASHBOARD_GROUP_FLAG VARCHAR2,
109 p_CURRENCY_CODE VARCHAR2,
110 p_LAST_UPDATE_DATE DATE,
111 p_LAST_UPDATED_BY NUMBER,
112 p_LAST_UPDATE_LOGIN NUMBER,
113 p_SEEDED_FLAG VARCHAR2,
114 p_APPLICATION_ID NUMBER,
115 p_DASHBOARD_GROUP_ID NUMBER)
116
117 IS
118 BEGIN
119 Update CSC_PROF_MODULE_GROUPS
120 SET
121 FORM_FUNCTION_ID = p_FORM_FUNCTION_ID,
122 RESPONSIBILITY_ID =p_RESPONSIBILITY_ID,
123 RESP_APPL_ID =p_RESP_APPL_ID,
124 FORM_FUNCTION_NAME=p_FORM_FUNCTION_NAME,
125 PARTY_TYPE = p_PARTY_TYPE,
126 GROUP_ID = p_GROUP_ID,
127 DASHBOARD_GROUP_FLAG = p_DASHBOARD_GROUP_FLAG,
128 CURRENCY_CODE = p_CURRENCY_CODE,
129 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
130 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
132 SEEDED_FLAG = p_SEEDED_FLAG,
133 APPLICATION_ID = p_APPLICATION_ID,
134 DASHBOARD_GROUP_ID = p_DASHBOARD_GROUP_ID
135 where MODULE_GROUP_ID = p_MODULE_GROUP_ID;
136
137 If (SQL%NOTFOUND) then
138 RAISE NO_DATA_FOUND;
139 End If;
140 END Update_Row;
141
142 PROCEDURE Delete_Row(
143 p_MODULE_GROUP_ID NUMBER)
144 IS
145 BEGIN
146 DELETE FROM CSC_PROF_MODULE_GROUPS
147 WHERE MODULE_GROUP_ID = p_MODULE_GROUP_ID;
148 If (SQL%NOTFOUND) then
149 RAISE NO_DATA_FOUND;
150 End If;
151 END Delete_Row;
152
153 PROCEDURE Lock_Row(
154 p_MODULE_GROUP_ID NUMBER,
155 p_FORM_FUNCTION_ID NUMBER,
156 p_FORM_FUNCTION_NAME VARCHAR2,
157 p_RESPONSIBILITY_ID NUMBER,
158 p_RESP_APPL_ID NUMBER,
159 p_PARTY_TYPE VARCHAR2,
160 p_GROUP_ID NUMBER,
161 p_DASHBOARD_GROUP_FLAG VARCHAR2,
162 p_CURRENCY_CODE VARCHAR2,
163 p_LAST_UPDATE_DATE DATE,
164 p_LAST_UPDATED_BY NUMBER,
165 p_CREATION_DATE DATE,
166 p_CREATED_BY NUMBER,
167 p_LAST_UPDATE_LOGIN NUMBER,
168 p_SEEDED_FLAG VARCHAR2,
169 p_APPLICATION_ID NUMBER,
170 p_DASHBOARD_GROUP_ID NUMBER)
171
172 IS
173 CURSOR C IS
174 SELECT *
175 FROM CSC_PROF_MODULE_GROUPS
176 WHERE MODULE_GROUP_ID = p_MODULE_GROUP_ID
177 FOR UPDATE of MODULE_GROUP_ID NOWAIT;
178 Recinfo C%ROWTYPE;
179 BEGIN
180 OPEN C;
181 FETCH C INTO Recinfo;
182 If (C%NOTFOUND) then
183 CLOSE C;
184 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 End If;
187 CLOSE C;
188 if (
189 ( Recinfo.MODULE_GROUP_ID = p_MODULE_GROUP_ID)
190 AND ( ( Recinfo.FORM_FUNCTION_NAME = p_FORM_FUNCTION_NAME)
191 OR ( ( Recinfo.FORM_FUNCTION_NAME IS NULL )
192 AND ( p_FORM_FUNCTION_NAME IS NULL )))
193 AND ( ( Recinfo.PARTY_TYPE = p_PARTY_TYPE)
194 OR ( ( Recinfo.PARTY_TYPE IS NULL )
195 AND ( p_PARTY_TYPE IS NULL )))
196 AND ( ( Recinfo.GROUP_ID = p_GROUP_ID)
197 OR ( ( Recinfo.GROUP_ID IS NULL )
198 AND ( p_GROUP_ID IS NULL )))
199 AND ( ( Recinfo.DASHBOARD_GROUP_ID = p_DASHBOARD_GROUP_ID)
200 OR ( ( Recinfo.DASHBOARD_GROUP_ID IS NULL )
201 AND ( p_DASHBOARD_GROUP_ID IS NULL )))
202 /* AND ( ( Recinfo.DASHBOARD_GROUP_FLAG = p_DASHBOARD_GROUP_FLAG)
203 OR ( ( Recinfo.DASHBOARD_GROUP_FLAG IS NULL )
204 AND ( p_DASHBOARD_GROUP_FLAG IS NULL ))) */
205 AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
206 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
207 AND ( p_CURRENCY_CODE IS NULL )))
208 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
209 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
210 AND ( p_LAST_UPDATE_DATE IS NULL )))
211 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
212 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
213 AND ( p_LAST_UPDATED_BY IS NULL )))
214 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
215 OR ( ( Recinfo.CREATION_DATE IS NULL )
216 AND ( p_CREATION_DATE IS NULL )))
217 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
218 OR ( ( Recinfo.CREATED_BY IS NULL )
219 AND ( p_CREATED_BY IS NULL )))
220 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
221 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
222 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
223 AND ( ( Recinfo.SEEDED_FLAG = p_SEEDED_FLAG)
224 OR ( ( Recinfo.SEEDED_FLAG IS NULL )
225 AND ( p_SEEDED_FLAG IS NULL )))
226 AND ( ( Recinfo.RESPONSIBILITY_ID = p_RESPONSIBILITY_ID)
227 OR ( ( Recinfo.RESPONSIBILITY_ID IS NULL )
228 AND ( p_RESPONSIBILITY_ID IS NULL )))
229 AND ( ( Recinfo.RESP_APPL_ID = p_RESP_APPL_ID)
230 OR ( ( Recinfo.RESP_APPL_ID IS NULL )
231 AND ( p_RESP_APPL_ID IS NULL )))
232 AND ( ( Recinfo.APPLICATION_ID = p_APPLICATION_ID)
233 OR ( ( Recinfo.APPLICATION_ID IS NULL )
234 AND ( p_APPLICATION_ID IS NULL )))
235 )
236
237 then
238 return;
239 else
240 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 End If;
243 END Lock_Row;
244
245 PROCEDURE Load_Row(
246 p_MODULE_GROUP_ID NUMBER,
247 p_FORM_FUNCTION_ID NUMBER,
248 p_FORM_FUNCTION_NAME VARCHAR2,
249 p_RESPONSIBILITY_ID NUMBER := NULL,
250 p_RESP_APPL_ID NUMBER := NULL,
251 p_PARTY_TYPE VARCHAR2,
252 p_GROUP_ID NUMBER,
253 p_DASHBOARD_GROUP_FLAG VARCHAR2,
254 p_CURRENCY_CODE VARCHAR2,
255 p_LAST_UPDATE_DATE DATE,
256 p_LAST_UPDATED_BY NUMBER,
257 p_LAST_UPDATE_LOGIN NUMBER,
258 p_SEEDED_FLAG VARCHAR2,
259 p_APPLICATION_ID NUMBER,
260 p_DASHBOARD_GROUP_ID NUMBER,
261 P_Owner VARCHAR2)
262 IS
263 l_user_id number := 0;
264 l_module_group_id number ;
265 Begin
266
267 l_module_group_id := p_module_group_id ;
268
269 Csc_Prof_Module_Groups_Pkg.Update_Row(
270 p_MODULE_GROUP_ID => p_module_group_id,
271 p_FORM_FUNCTION_ID => p_form_function_id,
272 p_FORM_FUNCTION_NAME => p_form_function_name,
273 p_RESPONSIBILITY_ID => p_responsibility_id,
274 p_RESP_APPL_ID => p_resp_appl_id,
275 p_PARTY_TYPE => p_party_type,
276 p_GROUP_ID => p_group_id,
277 p_DASHBOARD_GROUP_FLAG => p_dashboard_group_flag,
278 p_CURRENCY_CODE => p_currency_code,
279 p_LAST_UPDATE_DATE => p_last_update_date,
280 p_LAST_UPDATED_BY => p_last_updated_by,
281 p_LAST_UPDATE_LOGIN => 0,
282 p_SEEDED_FLAG => p_seeded_flag,
283 p_APPLICATION_ID => p_application_id,
284 p_DASHBOARD_GROUP_ID => p_dashboard_group_id);
285
286 EXCEPTION
287 WHEN NO_DATA_FOUND THEN
288
289 Csc_Prof_Module_Groups_Pkg.Insert_Row(
290 px_MODULE_GROUP_ID => l_module_group_id,
291 p_FORM_FUNCTION_ID => p_form_function_id,
292 p_FORM_FUNCTION_NAME => p_form_function_name,
293 p_RESPONSIBILITY_ID => p_responsibility_id,
294 p_RESP_APPL_ID => p_resp_appl_id,
295 p_PARTY_TYPE => p_party_type,
296 p_GROUP_ID => p_group_id,
297 p_DASHBOARD_GROUP_FLAG => p_dashboard_group_flag,
298 p_CURRENCY_CODE => p_currency_code,
299 p_LAST_UPDATE_DATE => p_last_update_date,
300 p_LAST_UPDATED_BY => p_last_updated_by,
301 p_CREATION_DATE => p_last_update_date,
302 p_CREATED_BY => p_last_updated_by,
303 p_LAST_UPDATE_LOGIN => 0,
304 p_SEEDED_FLAG => p_seeded_flag,
305 p_APPLICATION_ID => p_application_id,
306 p_DASHBOARD_GROUP_ID => p_dashboard_group_id);
307
308 End Load_Row;
309 End CSC_PROF_MODULE_GROUPS_PKG;