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