[Home] [Help]
PACKAGE BODY: APPS.CSC_PROF_GROUP_CATEGORIES_PKG
Source
1 PACKAGE BODY CSC_PROF_GROUP_CATEGORIES_PKG as
2 /* $Header: csctpcab.pls 120.3 2005/09/18 23:44:08 vshastry ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROF_GROUP_CATEGORIES_PKG
5 -- Purpose :
6 -- History :
7 -- 26 Nov 2002 JAmose For Fnd_Api.G_Miss* changes
8 -- 19 july 2005 tpalaniv Modified the load_row API to fetch last_updated_by using FND API
9 -- 19-09-2005 vshastry Bug 4596220. Added condition in insert row
10 -- NOTE :
11 -- End of Comments
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROF_GROUP_CATEGORIES_PKG';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctpgcb.pls';
16
17 PROCEDURE Insert_Row(
18 px_GROUP_CATEGORY_ID IN OUT NOCOPY NUMBER,
19 p_GROUP_ID NUMBER,
20 p_CATEGORY_CODE VARCHAR2,
21 p_CATEGORY_SEQUENCE NUMBER,
22 p_CREATED_BY NUMBER,
23 p_CREATION_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_LAST_UPDATE_DATE DATE,
26 p_LAST_UPDATE_LOGIN NUMBER,
27 p_SEEDED_FLAG VARCHAR2)
28
29 IS
30 CURSOR C2 IS SELECT CSC_PROF_GROUP_CATEGORIES_S.nextval FROM sys.dual;
31 ps_SEEDED_FLAG Varchar2(3);
32 BEGIN
33
34 /* added the below 2 lines for bug 4596220 */
35 ps_seeded_flag := p_seeded_flag;
36 IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
37
38 /* Added This If Condition for Bug 1944040*/
39 If p_Created_by=1 then
40 ps_seeded_flag:='Y';
41 Else
42 ps_seeded_flag:='N';
43 End If;
44 END IF;
45
46 If (px_GROUP_CATEGORY_ID IS NULL) OR (px_GROUP_CATEGORY_ID = FND_API.G_MISS_NUM) then
47 OPEN C2;
48 FETCH C2 INTO px_GROUP_CATEGORY_ID;
49 CLOSE C2;
50 End If;
51 INSERT INTO CSC_PROF_GROUP_CATEGORIES(
52 GROUP_CATEGORY_ID,
53 GROUP_ID,
54 CATEGORY_CODE,
55 CATEGORY_SEQUENCE,
56 CREATED_BY,
57 CREATION_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATE_LOGIN,
61 SEEDED_FLAG
62 ) VALUES (
63 px_GROUP_CATEGORY_ID,
64 decode( p_GROUP_ID, FND_API.G_MISS_NUM, NULL, p_GROUP_ID),
65 decode( p_CATEGORY_CODE, FND_API.G_MISS_CHAR, NULL, p_CATEGORY_CODE),
66 decode( p_CATEGORY_SEQUENCE, FND_API.G_MISS_NUM, NULL, p_CATEGORY_SEQUENCE),
67 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
68 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
69 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
70 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
71 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
72 decode( p_SEEDED_FLAG,CSC_CORE_UTILS_PVT.G_MISS_CHAR, NULL, ps_SEEDED_FLAG));
73 End Insert_Row;
74
75 PROCEDURE Update_Row(
76 p_GROUP_CATEGORY_ID NUMBER,
77 p_GROUP_ID NUMBER,
78 p_CATEGORY_CODE VARCHAR2,
79 p_CATEGORY_SEQUENCE NUMBER,
80 p_LAST_UPDATED_BY NUMBER,
81 p_LAST_UPDATE_DATE DATE,
82 p_LAST_UPDATE_LOGIN NUMBER,
83 p_SEEDED_FLAG VARCHAR2)
84
85 IS
86 BEGIN
87 Update CSC_PROF_GROUP_CATEGORIES
88 SET
89 GROUP_ID = p_GROUP_ID,
90 CATEGORY_CODE = p_CATEGORY_CODE,
91 CATEGORY_SEQUENCE =p_CATEGORY_SEQUENCE,
92 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
93 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
94 LAST_UPDATE_LOGIN =p_LAST_UPDATE_LOGIN,
95 SEEDED_FLAG= p_SEEDED_FLAG
96 where GROUP_CATEGORY_ID = p_GROUP_CATEGORY_ID;
97
98 If (SQL%NOTFOUND) then
99 RAISE NO_DATA_FOUND;
100 End If;
101 END Update_Row;
102
103 PROCEDURE Delete_Row(
104 p_GROUP_CATEGORY_ID NUMBER)
105 IS
106 BEGIN
107 DELETE FROM CSC_PROF_GROUP_CATEGORIES
108 WHERE GROUP_CATEGORY_ID = p_GROUP_CATEGORY_ID;
109 If (SQL%NOTFOUND) then
110 RAISE NO_DATA_FOUND;
111 End If;
112 END Delete_Row;
113
114 PROCEDURE Lock_Row(
115 p_GROUP_CATEGORY_ID NUMBER,
116 p_GROUP_ID NUMBER,
117 p_CATEGORY_CODE VARCHAR2,
118 p_CATEGORY_SEQUENCE NUMBER,
119 p_CREATED_BY NUMBER,
120 p_CREATION_DATE DATE,
121 p_LAST_UPDATED_BY NUMBER,
122 p_LAST_UPDATE_DATE DATE,
123 p_LAST_UPDATE_LOGIN NUMBER,
124 p_SEEDED_FLAG VARCHAR2)
125
126 IS
127 CURSOR C IS
128 SELECT *
129 FROM CSC_PROF_GROUP_CATEGORIES
130 WHERE GROUP_CATEGORY_ID = p_GROUP_CATEGORY_ID
131 FOR UPDATE of GROUP_CATEGORY_ID NOWAIT;
132 Recinfo C%ROWTYPE;
133 BEGIN
134 OPEN C;
135 FETCH C INTO Recinfo;
136 If (C%NOTFOUND) then
137 CLOSE C;
138 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
139 APP_EXCEPTION.RAISE_EXCEPTION;
140 End If;
141 CLOSE C;
142 if (
143 ( Recinfo.GROUP_CATEGORY_ID = p_GROUP_CATEGORY_ID)
144 AND ( ( Recinfo.GROUP_ID = p_GROUP_ID)
145 OR ( ( Recinfo.GROUP_ID IS NULL )
146 AND ( p_GROUP_ID IS NULL )))
147 AND ( ( Recinfo.CATEGORY_CODE = p_CATEGORY_CODE)
148 OR ( ( Recinfo.CATEGORY_CODE IS NULL )
149 AND ( p_CATEGORY_CODE IS NULL )))
150 AND ( ( Recinfo.CATEGORY_SEQUENCE = p_CATEGORY_SEQUENCE)
151 OR ( ( Recinfo.CATEGORY_SEQUENCE IS NULL )
152 AND ( p_CATEGORY_SEQUENCE IS NULL )))
153 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
154 OR ( ( Recinfo.CREATED_BY IS NULL )
155 AND ( p_CREATED_BY IS NULL )))
156 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
157 OR ( ( Recinfo.CREATION_DATE IS NULL )
158 AND ( p_CREATION_DATE IS NULL )))
159 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
160 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
161 AND ( p_LAST_UPDATED_BY IS NULL )))
162 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
163 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
164 AND ( p_LAST_UPDATE_DATE IS NULL )))
165 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
166 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
167 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
168 AND ( ( Recinfo.SEEDED_FLAG = p_SEEDED_FLAG)
169 OR ( ( Recinfo.SEEDED_FLAG IS NULL )
170 AND ( p_SEEDED_FLAG IS NULL )))
171
172 ) then
173 return;
174 else
175 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
176 APP_EXCEPTION.RAISE_EXCEPTION;
177 End If;
178 END Lock_Row;
179
180 Procedure LOAD_ROW (
181 p_GROUP_CATEGORY_ID in number,
182 p_GROUP_ID in number,
183 p_CATEGORY_CODE in varchar2,
184 p_CATEGORY_SEQUENCE in number ,
185 p_SEEDED_FLAG in varchar2,
186 p_last_updated_by IN NUMBER,
187 p_last_update_date IN DATE
188 ) is
189
190 l_user_id number := 0;
191 l_group_category_id number := p_GROUP_CATEGORY_ID;
192
193 Begin
194
195 Csc_Prof_Group_Categories_Pkg.Update_Row(
196 p_GROUP_CATEGORY_ID => p_GROUP_CATEGORY_ID,
197 p_GROUP_ID => p_GROUP_ID,
198 p_CATEGORY_CODE => p_CATEGORY_CODE,
199 p_CATEGORY_SEQUENCE => p_CATEGORY_SEQUENCE,
200 p_LAST_UPDATED_BY => p_last_updated_by,
201 p_LAST_UPDATE_DATE => p_last_update_date,
202 p_LAST_UPDATE_LOGIN => 0,
203 p_SEEDED_FLAG => p_SEEDED_FLAG);
204
205 Exception
206 When no_data_found then
207 Csc_Prof_Group_Categories_Pkg.Insert_Row(
208 px_GROUP_CATEGORY_ID => l_group_category_id,
209 p_GROUP_ID => p_GROUP_ID,
210 p_CATEGORY_CODE => p_CATEGORY_CODE,
211 p_CATEGORY_SEQUENCE => p_CATEGORY_SEQUENCE,
212 p_CREATED_BY => p_last_updated_by,
213 p_CREATION_DATE => p_last_update_date,
214 p_LAST_UPDATED_BY => p_last_updated_by,
215 p_LAST_UPDATE_DATE => p_last_update_date,
216 p_LAST_UPDATE_LOGIN => 0,
217 p_SEEDED_FLAG => p_SEEDED_FLAG );
218
219 End Load_Row;
220
221
222 End CSC_PROF_GROUP_CATEGORIES_PKG;