[Home] [Help]
PACKAGE BODY: APPS.CSC_PROF_CHECK_RATINGS_PKG
Source
1 PACKAGE BODY CSC_PROF_CHECK_RATINGS_PKG as
2 /* $Header: csctprab.pls 120.5 2006/07/21 06:13:23 adhanara ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROF_CHECK_RATINGS_PKG
5 -- Purpose :
6 -- History :
7 -- 03 Nov 00 axsubram Added load_row (# 1487338)
8 -- 03 Nov 00 axsubram File name constant corrected to csctprab.pls
9 -- 07 Nov 02 jamose Upgrade table handler changes
10 -- 26 Nov 02 jamose made changes for the NOCOPY and FND_API.G_MISS*
11 -- 19 july 2005 tpalaniv Modified the load_row API to fetch last_updated_by using FND API
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_CHECK_RATINGS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csctprab.pls';
19
20 G_MISS_CHAR VARCHAR2(1) := FND_API.G_MISS_CHAR;
21 G_MISS_NUM NUMBER := FND_API.G_MISS_NUM;
22 G_MISS_DATE DATE := FND_API.G_MISS_DATE;
23
24 PROCEDURE Insert_Row(
25 px_CHECK_RATING_ID IN OUT NOCOPY NUMBER,
26 p_CHECK_ID NUMBER,
27 p_CHECK_RATING_GRADE VARCHAR2,
28 p_RATING_CODE VARCHAR2,
29 p_COLOR_CODE VARCHAR2,
30 p_RANGE_LOW_VALUE VARCHAR2,
31 p_RANGE_HIGH_VALUE VARCHAR2,
32 p_LAST_UPDATE_DATE DATE,
33 p_LAST_UPDATED_BY NUMBER,
34 p_CREATION_DATE DATE,
35 p_CREATED_BY NUMBER,
36 p_LAST_UPDATE_LOGIN NUMBER,
37 p_SEEDED_FLAG VARCHAR2)
38
39 IS
40 CURSOR C2 IS SELECT CSC_PROF_CHECK_RATINGS_S.nextval FROM sys.dual;
41 ps_SEEDED_FLAG Varchar2(3);
42 BEGIN
43
44 /* added the below 2 lines for bug 4596220 */
45 ps_seeded_flag := p_seeded_flag;
46 IF NVL(p_seeded_flag, 'N') <> 'Y' THEN
47
48 /* Added This If Condition for Bug 1944040*/
49 If p_Created_by=1 then
50 ps_seeded_flag:='Y';
51 Else
52 ps_seeded_flag:='N';
53 End If;
54 END IF;
55
56 If (px_CHECK_RATING_ID IS NULL) OR (px_CHECK_RATING_ID = G_MISS_NUM) then
57 OPEN C2;
58 FETCH C2 INTO px_CHECK_RATING_ID;
59 CLOSE C2;
60 End If;
61 INSERT INTO CSC_PROF_CHECK_RATINGS(
62 CHECK_RATING_ID,
63 CHECK_ID,
64 CHECK_RATING_GRADE,
65 RATING_CODE,
66 COLOR_CODE,
67 RANGE_LOW_VALUE,
68 RANGE_HIGH_VALUE,
69 LAST_UPDATE_DATE,
70 LAST_UPDATED_BY,
71 CREATION_DATE,
72 CREATED_BY,
73 LAST_UPDATE_LOGIN,
74 SEEDED_FLAG
75 ) VALUES (
76 px_CHECK_RATING_ID,
77 decode( p_CHECK_ID, G_MISS_NUM, NULL, p_CHECK_ID),
78 decode( p_CHECK_RATING_GRADE, G_MISS_CHAR, NULL, p_CHECK_RATING_GRADE),
79 decode( p_RATING_CODE, G_MISS_CHAR, NULL, p_RATING_CODE),
80 decode( p_COLOR_CODE, G_MISS_CHAR, NULL, p_COLOR_CODE),
81 decode( p_RANGE_LOW_VALUE, G_MISS_CHAR, NULL, p_RANGE_LOW_VALUE),
82 decode( p_RANGE_HIGH_VALUE,G_MISS_CHAR, NULL, p_RANGE_HIGH_VALUE),
83 decode( p_LAST_UPDATE_DATE, G_MISS_DATE, to_date(NULL), p_LAST_UPDATE_DATE),
84 decode( p_LAST_UPDATED_BY, G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
85 decode( p_CREATION_DATE, G_MISS_DATE, to_date(NULL), p_CREATION_DATE),
86 decode( p_CREATED_BY, G_MISS_NUM, NULL, p_CREATED_BY),
87 decode( p_LAST_UPDATE_LOGIN, G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
88 decode( p_SEEDED_FLAG, G_MISS_CHAR, NULL, ps_SEEDED_FLAG));
89 End Insert_Row;
90
91 PROCEDURE Update_Row(
92 p_CHECK_RATING_ID NUMBER,
93 p_CHECK_ID NUMBER,
94 p_CHECK_RATING_GRADE VARCHAR2,
95 p_RATING_CODE VARCHAR2,
96 p_COLOR_CODE VARCHAR2,
97 p_RANGE_LOW_VALUE VARCHAR2,
98 p_RANGE_HIGH_VALUE VARCHAR2,
99 p_LAST_UPDATE_DATE DATE,
100 p_LAST_UPDATED_BY NUMBER,
101 p_LAST_UPDATE_LOGIN NUMBER,
102 p_SEEDED_FLAG VARCHAR2)
103
104 IS
105 BEGIN
106 Update CSC_PROF_CHECK_RATINGS
107 SET
108 CHECK_ID = p_CHECK_ID,
109 CHECK_RATING_GRADE = p_CHECK_RATING_GRADE,
110 RATING_CODE = p_RATING_CODE,
111 COLOR_CODE = p_COLOR_CODE,
112 RANGE_LOW_VALUE = p_RANGE_LOW_VALUE,
113 RANGE_HIGH_VALUE = p_RANGE_HIGH_VALUE,
114 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
115 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
116 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
117 SEEDED_FLAG = p_SEEDED_FLAG
118 where CHECK_RATING_ID = p_CHECK_RATING_ID;
119
120 If (SQL%NOTFOUND) then
121 RAISE NO_DATA_FOUND;
122 End If;
123 END Update_Row;
124
125
126 PROCEDURE Lock_Row(
127 p_CHECK_RATING_ID NUMBER,
128 p_CHECK_ID NUMBER,
129 p_CHECK_RATING_GRADE VARCHAR2,
130 --p_RATING_COLOR_ID NUMBER,
131 p_RATING_CODE VARCHAR2,
132 p_COLOR_CODE VARCHAR2,
133 p_RANGE_LOW_VALUE VARCHAR2,
134 p_RANGE_HIGH_VALUE VARCHAR2,
135 p_LAST_UPDATE_DATE DATE,
136 p_LAST_UPDATED_BY NUMBER,
137 p_CREATION_DATE DATE,
138 p_CREATED_BY NUMBER,
139 p_LAST_UPDATE_LOGIN NUMBER,
140 p_SEEDED_FLAG VARCHAR2)
141
142 IS
143 CURSOR C IS
144 SELECT *
145 FROM CSC_PROF_CHECK_RATINGS
146 WHERE CHECK_RATING_ID = p_CHECK_RATING_ID
147 FOR UPDATE of CHECK_RATING_ID NOWAIT;
148 Recinfo C%ROWTYPE;
149 BEGIN
150 OPEN C;
151 FETCH C INTO Recinfo;
152 If (C%NOTFOUND) then
153 CLOSE C;
154 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
155 APP_EXCEPTION.RAISE_EXCEPTION;
156 End If;
157 CLOSE C;
158 /* Bug 5245779 Only auditable columns in the table need to be verified */
159 if (
160 ( Recinfo.CHECK_RATING_ID = p_CHECK_RATING_ID)
161 AND ( ( Recinfo.CHECK_ID = p_CHECK_ID)
162 OR ( ( Recinfo.CHECK_ID IS NULL )
163 AND ( p_CHECK_ID IS NULL )))
164 AND ( ( to_char(Recinfo.LAST_UPDATE_DATE,'dd-mon-rrrr') = to_char(p_LAST_UPDATE_DATE,'dd-mon-rrrr'))
165 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
166 AND ( p_LAST_UPDATE_DATE IS NULL )))
167 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
168 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
169 AND ( p_LAST_UPDATED_BY IS NULL )))
170 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
171 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
172 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
173
174 ) then
175 return;
176 else
177 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
178 APP_EXCEPTION.RAISE_EXCEPTION;
179 End If;
180 END Lock_Row;
181
182
183
184
185 PROCEDURE Delete_Row(
186 p_CHECK_RATING_ID NUMBER)
187 IS
188 BEGIN
189 DELETE FROM CSC_PROF_CHECK_RATINGS
190 WHERE CHECK_RATING_ID = p_CHECK_RATING_ID;
191 If (SQL%NOTFOUND) then
192 RAISE NO_DATA_FOUND;
193 End If;
194 END Delete_Row;
195
196 PROCEDURE Load_Row(
197 p_CHECK_RATING_ID NUMBER,
198 p_CHECK_ID NUMBER,
199 p_CHECK_RATING_GRADE VARCHAR2,
200 p_RATING_CODE VARCHAR2,
201 p_COLOR_CODE VARCHAR2,
202 p_RANGE_LOW_VALUE VARCHAR2,
203 p_RANGE_HIGH_VALUE VARCHAR2,
204 p_LAST_UPDATE_DATE DATE,
205 p_LAST_UPDATED_BY NUMBER,
206 p_LAST_UPDATE_LOGIN NUMBER,
207 p_SEEDED_FLAG VARCHAR2,
208 P_Owner VARCHAR2)
209 IS
210 l_user_id number := 0;
211 l_check_rating_id number := G_MISS_NUM;
212
213 /** This is mainly for loading seed data . That is the
214 reason, that l_check_rating_id is being declared here, The
215 check_rating_id returned from insert_row is not used.
216
217 **/
218 BEGIN
219
220 l_check_rating_id := p_check_rating_id;
221
222 Csc_Prof_Check_Ratings_Pkg.Update_Row(
223 p_CHECK_RATING_ID => p_check_rating_id,
224 p_CHECK_ID => p_check_id,
225 p_CHECK_RATING_GRADE => p_check_rating_grade,
226 p_RATING_CODE => p_rating_code,
227 p_COLOR_CODE => p_color_code,
228 p_RANGE_LOW_VALUE => p_range_low_value,
229 p_RANGE_HIGH_VALUE => p_range_high_value,
230 p_LAST_UPDATE_DATE => p_last_update_date,
231 p_LAST_UPDATED_BY => p_last_updated_by,
232 p_LAST_UPDATE_LOGIN => 0,
233 p_SEEDED_FLAG => p_seeded_flag);
234
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237
238 Csc_Prof_Check_Ratings_Pkg.Insert_Row(
239 px_CHECK_RATING_ID => l_check_rating_id,
240 p_CHECK_ID => p_check_id,
241 p_CHECK_RATING_GRADE => p_check_rating_grade,
242 p_RATING_CODE => p_rating_code,
243 p_COLOR_CODE => p_color_code,
244 p_RANGE_LOW_VALUE => p_range_low_value,
245 p_RANGE_HIGH_VALUE => p_range_high_value,
246 p_LAST_UPDATE_DATE => p_last_update_date,
247 p_LAST_UPDATED_BY => p_last_updated_by,
248 p_CREATION_DATE => p_last_update_date,
249 p_CREATED_BY => p_last_updated_by,
250 p_LAST_UPDATE_LOGIN => 0,
251 p_SEEDED_FLAG => p_seeded_flag);
252
253
254 End Load_Row;
255
256
257 End CSC_PROF_CHECK_RATINGS_PKG;