[Home] [Help]
PACKAGE BODY: APPS.CSC_PROF_GROUP_CHECKS_PKG
Source
1 PACKAGE BODY CSC_PROF_GROUP_CHECKS_PKG as
2 /* $Header: csctpgcb.pls 120.1 2005/08/03 22:58:08 mmadhavi noship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROF_GROUP_CHECKS_PKG
5 -- Purpose :
6 -- History :
7 -- 07 Nov 02 jamose Upgrade table handler changes
8 -- 27 Nov 02 jamose For Fnd_Api_G_Miss* and NOCOPY changes
9 -- NOTE :
10 -- End of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_PROF_GROUP_CHECKS_PKG';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctugcb.pls';
15
16 G_MISS_CHAR VARCHAR2(1) := FND_API.G_MISS_CHAR;
17 G_MISS_NUM NUMBER := FND_API.G_MISS_NUM;
18 G_MISS_DATE DATE := FND_API.G_MISS_DATE;
19
20 PROCEDURE Insert_Row(
21 p_GROUP_ID NUMBER,
22 p_CHECK_ID NUMBER,
23 p_CHECK_SEQUENCE NUMBER,
24 p_END_DATE_ACTIVE DATE,
25 p_START_DATE_ACTIVE DATE,
26 p_CATEGORY_CODE VARCHAR2,
27 p_CATEGORY_SEQUENCE NUMBER,
28 p_THRESHOLD_FLAG VARCHAR2,
29 p_CRITICAL_FLAG VARCHAR2,
30 p_SEEDED_FLAG VARCHAR2,
31 p_CREATED_BY NUMBER,
32 p_CREATION_DATE DATE,
33 p_LAST_UPDATED_BY NUMBER,
34 p_LAST_UPDATE_DATE DATE,
35 p_LAST_UPDATE_LOGIN NUMBER)
36
37 IS
38 CURSOR C2 IS SELECT MAX(nvl(check_sequence,0)) + 1
39 FROM CSC_PROF_GROUP_CHECKS
40 WHERE check_id = p_CHECK_ID;
41 l_check_Sequence Number;
42 ps_SEEDED_FLAG Varchar2(3);
43
44 BEGIN
45
46 /* Added This If Condition for Bug 1944040*/
47 If p_Created_by=1 then
48 ps_seeded_flag:='Y';
49 Else
50 ps_seeded_flag:=p_seeded_flag;
51 End If;
52
53 -- If (p_GROUP_ID IS NULL) OR (p_GROUP_ID = CSC_CORE_UTILS_PVT.G_MISS_NUM) then
54 -- OPEN C2;
55 -- FETCH C2 INTO px_check_sequence;
56 -- CLOSE C2;
57 -- End If;
58 INSERT INTO CSC_PROF_GROUP_CHECKS(
59 GROUP_ID,
60 CHECK_ID,
61 CHECK_SEQUENCE,
62 END_DATE_ACTIVE,
63 START_DATE_ACTIVE,
64 CATEGORY_CODE,
65 CATEGORY_SEQUENCE,
66 THRESHOLD_FLAG,
67 CRITICAL_FLAG,
68 CREATED_BY,
69 CREATION_DATE,
70 LAST_UPDATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATE_LOGIN,
73 SEEDED_FLAG
74 ) VALUES (
75 p_GROUP_ID,
76 decode( p_CHECK_ID, G_MISS_NUM, NULL, p_CHECK_ID),
77 decode(p_CHECK_SEQUENCE,G_MISS_NUM,NULL,P_CHECK_SEQUENCE),
78 decode( p_END_DATE_ACTIVE, G_MISS_DATE, NULL, p_END_DATE_ACTIVE),
79 decode( p_START_DATE_ACTIVE, G_MISS_DATE, NULL, p_START_DATE_ACTIVE),
80 decode( p_CATEGORY_CODE, G_MISS_CHAR, NULL, p_CATEGORY_CODE),
81 decode( p_CATEGORY_SEQUENCE,G_MISS_NUM, NULL, p_CATEGORY_SEQUENCE),
82 decode( p_THRESHOLD_FLAG, G_MISS_CHAR, NULL, p_THRESHOLD_FLAG),
83 decode( p_CRITICAL_FLAG, G_MISS_CHAR, NULL, p_CRITICAL_FLAG),
84 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
85 decode( p_CREATION_DATE,G_MISS_DATE, NULL, p_CREATION_DATE),
86 decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
87 decode( p_LAST_UPDATE_DATE,G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
88 decode( p_LAST_UPDATE_LOGIN,G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
89 decode( p_SEEDED_FLAG, G_MISS_CHAR, NULL, ps_SEEDED_FLAG) );
90 End Insert_Row;
91
92 PROCEDURE Update_Row(
93 p_GROUP_ID NUMBER,
94 p_CHECK_ID NUMBER,
95 p_CHECK_SEQUENCE NUMBER,
96 p_END_DATE_ACTIVE DATE,
97 p_START_DATE_ACTIVE DATE,
98 p_CATEGORY_CODE VARCHAR2,
99 p_CATEGORY_SEQUENCE NUMBER,
100 p_THRESHOLD_FLAG VARCHAR2,
101 p_CRITICAL_FLAG VARCHAR2,
102 p_SEEDED_FLAG VARCHAR2,
103 p_LAST_UPDATED_BY NUMBER,
104 p_LAST_UPDATE_DATE DATE,
105 p_LAST_UPDATE_LOGIN NUMBER)
106
107 IS
108 BEGIN
109 Update CSC_PROF_GROUP_CHECKS
110 SET
111 CHECK_SEQUENCE = p_CHECK_SEQUENCE,
112 END_DATE_ACTIVE = p_END_DATE_ACTIVE,
113 START_DATE_ACTIVE = p_START_DATE_ACTIVE,
114 CATEGORY_CODE = p_CATEGORY_CODE,
115 CATEGORY_SEQUENCE = p_CATEGORY_SEQUENCE,
116 THRESHOLD_FLAG = p_THRESHOLD_FLAG,
117 CRITICAL_FLAG = p_CRITICAL_FLAG,
118 SEEDED_FLAG = p_SEEDED_FLAG,
119 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
120 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
121 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
122 where GROUP_ID = p_GROUP_ID
123 and check_id = p_check_id;
124
125 If (SQL%NOTFOUND) then
126 RAISE NO_DATA_FOUND;
127 End If;
128 END Update_Row;
129
130
131 PROCEDURE Lock_Row(
132 p_GROUP_ID NUMBER,
133 p_CHECK_ID NUMBER,
134 p_CHECK_SEQUENCE NUMBER,
135 p_END_DATE_ACTIVE DATE,
136 p_START_DATE_ACTIVE DATE,
137 p_CATEGORY_CODE VARCHAR2,
138 p_CATEGORY_SEQUENCE NUMBER,
139 p_THRESHOLD_FLAG VARCHAR2,
140 p_CRITICAL_FLAG VARCHAR2,
141 p_SEEDED_FLAG VARCHAR2,
142 p_CREATED_BY NUMBER,
143 p_CREATION_DATE DATE,
144 p_LAST_UPDATED_BY NUMBER,
145 p_LAST_UPDATE_DATE DATE,
146 p_LAST_UPDATE_LOGIN NUMBER)
147
148 IS
149 CURSOR C IS
150 SELECT *
151 FROM CSC_PROF_GROUP_CHECKS
152 WHERE GROUP_ID = p_GROUP_ID
153 AND CHECK_ID = P_CHECK_ID
154 FOR UPDATE of GROUP_ID NOWAIT;
155 Recinfo C%ROWTYPE;
156 BEGIN
157 OPEN C;
158 FETCH C INTO Recinfo;
159 If (C%NOTFOUND) then
160 CLOSE C;
161 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
162 APP_EXCEPTION.RAISE_EXCEPTION;
163 End If;
164 CLOSE C;
165 if (
166 ( Recinfo.GROUP_ID = p_GROUP_ID)
167 AND ( ( Recinfo.CHECK_ID = p_CHECK_ID)
168 OR ( ( Recinfo.CHECK_ID IS NULL )
169 AND ( p_CHECK_ID IS NULL )))
170 AND ( ( Recinfo.CHECK_SEQUENCE = p_CHECK_SEQUENCE)
171 OR ( ( Recinfo.CHECK_SEQUENCE IS NULL )
172 AND ( p_CHECK_SEQUENCE IS NULL )))
173 AND ( ( Recinfo.END_DATE_ACTIVE = p_END_DATE_ACTIVE)
174 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
175 AND ( p_END_DATE_ACTIVE IS NULL )))
176 AND ( ( Recinfo.START_DATE_ACTIVE = p_START_DATE_ACTIVE)
177 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
178 AND ( p_START_DATE_ACTIVE IS NULL )))
179 AND ( ( Recinfo.CATEGORY_CODE = p_CATEGORY_CODE)
180 OR ( ( Recinfo.CATEGORY_CODE IS NULL )
181 AND ( p_CATEGORY_CODE IS NULL )))
182 AND ( ( Recinfo.CATEGORY_SEQUENCE = p_CATEGORY_SEQUENCE)
183 OR ( ( Recinfo.CATEGORY_SEQUENCE IS NULL )
184 AND ( p_CATEGORY_SEQUENCE IS NULL )))
185 AND ( ( Recinfo.THRESHOLD_FLAG = p_THRESHOLD_FLAG)
186 OR ( ( Recinfo.THRESHOLD_FLAG IS NULL )
187 AND ( p_THRESHOLD_FLAG IS NULL )))
188 AND ( ( Recinfo.CRITICAL_FLAG = p_CRITICAL_FLAG)
189 OR ( ( Recinfo.CRITICAL_FLAG IS NULL )
190 AND ( p_CRITICAL_FLAG IS NULL )))
191 AND ( ( Recinfo.SEEDED_FLAG = p_SEEDED_FLAG)
192 OR ( ( Recinfo.SEEDED_FLAG IS NULL )
193 AND ( p_SEEDED_FLAG IS NULL )))
194 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
195 OR ( ( Recinfo.CREATED_BY IS NULL )
196 AND ( p_CREATED_BY IS NULL )))
197 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
198 OR ( ( Recinfo.CREATION_DATE IS NULL )
199 AND ( p_CREATION_DATE IS NULL )))
200 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
201 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
202 AND ( p_LAST_UPDATED_BY IS NULL )))
203 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
204 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
205 AND ( p_LAST_UPDATE_DATE IS NULL )))
206 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
207 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
208 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
209 ) then
210 return;
211 else
212 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
213 APP_EXCEPTION.RAISE_EXCEPTION;
214 End If;
215 END Lock_Row;
216
217
218 PROCEDURE Delete_Row(
219 p_GROUP_ID NUMBER,
220 p_CHECK_ID NUMBER,
221 p_CHECK_SEQUENCE NUMBER)
222 IS
223 BEGIN
224 DELETE FROM CSC_PROF_GROUP_CHECKS
225 WHERE GROUP_ID = p_GROUP_ID
226 AND CHECK_ID = p_CHECK_ID
227 AND CHECK_SEQUENCE = p_CHECK_SEQUENCE;
228
229 If (SQL%NOTFOUND) then
230 RAISE NO_DATA_FOUND;
231 End If;
232 END Delete_Row;
233
234
235 End CSC_PROF_GROUP_CHECKS_PKG;