[Home] [Help]
PACKAGE BODY: APPS.HZ_CERTIFICATIONS_PKG
Source
1 PACKAGE BODY HZ_CERTIFICATIONS_PKG as
2 /* $Header: ARHOCETB.pls 120.3 2005/10/30 04:20:45 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_Rowid IN OUT NOCOPY VARCHAR2,
7 x_CERTIFICATION_ID NUMBER,
8 x_CERTIFICATION_NAME VARCHAR2,
9 x_CURRENT_STATUS VARCHAR2,
10 x_PARTY_ID NUMBER,
11 x_EXPIRES_ON_DATE DATE,
12 x_GRADE VARCHAR2,
13 x_ISSUED_BY_AUTHORITY VARCHAR2,
14 x_ISSUED_ON_DATE DATE,
15 x_CREATED_BY NUMBER,
16 x_CREATION_DATE DATE,
17 x_LAST_UPDATE_LOGIN NUMBER,
18 x_LAST_UPDATE_DATE DATE,
19 x_LAST_UPDATED_BY NUMBER,
20 x_REQUEST_ID NUMBER,
21 x_PROGRAM_APPLICATION_ID NUMBER,
22 x_PROGRAM_ID NUMBER,
23 x_PROGRAM_UPDATE_DATE DATE,
24 x_WH_UPDATE_DATE DATE,
25 x_STATUS VARCHAR2
26 ) IS
27 CURSOR C IS SELECT rowid FROM HZ_CERTIFICATIONS
28 WHERE CERTIFICATION_ID = x_CERTIFICATION_ID;
29 BEGIN
30 INSERT INTO HZ_CERTIFICATIONS(
31 CERTIFICATION_ID,
32 CERTIFICATION_NAME,
33 CURRENT_STATUS,
34 PARTY_ID,
35 EXPIRES_ON_DATE,
36 GRADE,
37 ISSUED_BY_AUTHORITY,
38 ISSUED_ON_DATE,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATE_LOGIN,
42 LAST_UPDATE_DATE,
43 LAST_UPDATED_BY,
44 REQUEST_ID,
45 PROGRAM_APPLICATION_ID,
46 PROGRAM_ID,
47 PROGRAM_UPDATE_DATE,
48 WH_UPDATE_DATE,
49 STATUS
50 ) VALUES (
51 x_CERTIFICATION_ID,
52 decode( x_CERTIFICATION_NAME, FND_API.G_MISS_CHAR, NULL,x_CERTIFICATION_NAME),
53 decode( x_CURRENT_STATUS, FND_API.G_MISS_CHAR, NULL,x_CURRENT_STATUS),
54 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
55 decode( x_EXPIRES_ON_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_EXPIRES_ON_DATE),
56 decode( x_GRADE, FND_API.G_MISS_CHAR, NULL,x_GRADE),
57 decode( x_ISSUED_BY_AUTHORITY, FND_API.G_MISS_CHAR, NULL,x_ISSUED_BY_AUTHORITY),
58 decode( x_ISSUED_ON_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_ISSUED_ON_DATE),
59 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
60 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
61 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
62 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
63 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
64 decode( x_REQUEST_ID, FND_API.G_MISS_NUM, NULL,x_REQUEST_ID),
65 decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_APPLICATION_ID),
66 decode( x_PROGRAM_ID, FND_API.G_MISS_NUM, NULL,x_PROGRAM_ID),
67 decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_PROGRAM_UPDATE_DATE),
68 decode( x_WH_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_WH_UPDATE_DATE),
69 decode(x_STATUS,FND_API.G_MISS_CHAR,'A',x_STATUS));
70 OPEN C;
71 FETCH C INTO x_Rowid;
72 If (C%NOTFOUND) then
73 CLOSE C;
74 RAISE NO_DATA_FOUND;
75 End If;
76 End Insert_Row;
77
78
79
80 PROCEDURE Delete_Row( x_CERTIFICATION_ID NUMBER
81 ) IS
82 BEGIN
83 DELETE FROM HZ_CERTIFICATIONS
84 WHERE CERTIFICATION_ID = x_CERTIFICATION_ID;
85 If (SQL%NOTFOUND) then
86 RAISE NO_DATA_FOUND;
87 End If;
88 END Delete_Row;
89
90
91
92 PROCEDURE Update_Row(
93 x_Rowid IN OUT NOCOPY VARCHAR2,
94 x_CERTIFICATION_ID NUMBER,
95 x_CERTIFICATION_NAME VARCHAR2,
96 x_CURRENT_STATUS VARCHAR2,
97 x_PARTY_ID NUMBER,
98 x_EXPIRES_ON_DATE DATE,
99 x_GRADE VARCHAR2,
100 x_ISSUED_BY_AUTHORITY VARCHAR2,
101 x_ISSUED_ON_DATE DATE,
102 x_CREATED_BY NUMBER,
103 x_CREATION_DATE DATE,
104 x_LAST_UPDATE_LOGIN NUMBER,
105 x_LAST_UPDATE_DATE DATE,
106 x_LAST_UPDATED_BY NUMBER,
107 x_REQUEST_ID NUMBER,
108 x_PROGRAM_APPLICATION_ID NUMBER,
109 x_PROGRAM_ID NUMBER,
110 x_PROGRAM_UPDATE_DATE DATE,
111 x_WH_UPDATE_DATE DATE,
112 x_STATUS VARCHAR2
113 ) IS
114 BEGIN
115 Update HZ_CERTIFICATIONS
116 SET
117 CERTIFICATION_ID = decode( x_CERTIFICATION_ID, FND_API.G_MISS_NUM,CERTIFICATION_ID,x_CERTIFICATION_ID),
118 CERTIFICATION_NAME = decode( x_CERTIFICATION_NAME, FND_API.G_MISS_CHAR,CERTIFICATION_NAME,x_CERTIFICATION_NAME),
119 CURRENT_STATUS = decode( x_CURRENT_STATUS, FND_API.G_MISS_CHAR,CURRENT_STATUS,x_CURRENT_STATUS),
120 PARTY_ID = decode( x_PARTY_ID, FND_API.G_MISS_NUM,PARTY_ID,x_PARTY_ID),
121 EXPIRES_ON_DATE = decode( x_EXPIRES_ON_DATE, FND_API.G_MISS_DATE,EXPIRES_ON_DATE,x_EXPIRES_ON_DATE),
122 GRADE = decode( x_GRADE, FND_API.G_MISS_CHAR,GRADE,x_GRADE),
123 ISSUED_BY_AUTHORITY = decode( x_ISSUED_BY_AUTHORITY, FND_API.G_MISS_CHAR,ISSUED_BY_AUTHORITY,x_ISSUED_BY_AUTHORITY),
124 ISSUED_ON_DATE = decode( x_ISSUED_ON_DATE, FND_API.G_MISS_DATE,ISSUED_ON_DATE,x_ISSUED_ON_DATE),
125 -- Bug 3032780
126 /*
127 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
128 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
129 */
130 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
131 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
132 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
133 REQUEST_ID = decode( x_REQUEST_ID, FND_API.G_MISS_NUM,REQUEST_ID,x_REQUEST_ID),
134 PROGRAM_APPLICATION_ID = decode( x_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM,PROGRAM_APPLICATION_ID,x_PROGRAM_APPLICATION_ID),
135 PROGRAM_ID = decode( x_PROGRAM_ID, FND_API.G_MISS_NUM,PROGRAM_ID,x_PROGRAM_ID),
136 PROGRAM_UPDATE_DATE = decode( x_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE,PROGRAM_UPDATE_DATE,x_PROGRAM_UPDATE_DATE),
137 WH_UPDATE_DATE = decode( x_WH_UPDATE_DATE, FND_API.G_MISS_DATE,WH_UPDATE_DATE,x_WH_UPDATE_DATE),
138
139 STATUS = decode( x_STATUS,FND_API.G_MISS_char,STATUS,x_STATUS)
140 where rowid = X_RowId;
141
142 If (SQL%NOTFOUND) then
143 RAISE NO_DATA_FOUND;
144 End If;
145 END Update_Row;
146
147
148
149 PROCEDURE Lock_Row(
150 x_Rowid VARCHAR2,
151 x_CERTIFICATION_ID NUMBER,
152 x_CERTIFICATION_NAME VARCHAR2,
153 x_CURRENT_STATUS VARCHAR2,
154 x_PARTY_ID NUMBER,
155 x_EXPIRES_ON_DATE DATE,
156 x_GRADE VARCHAR2,
157 x_ISSUED_BY_AUTHORITY VARCHAR2,
158 x_ISSUED_ON_DATE DATE,
159 x_CREATED_BY NUMBER,
160 x_CREATION_DATE DATE,
161 x_LAST_UPDATE_LOGIN NUMBER,
162 x_LAST_UPDATE_DATE DATE,
163 x_LAST_UPDATED_BY NUMBER,
164 x_REQUEST_ID NUMBER,
165 x_PROGRAM_APPLICATION_ID NUMBER,
166 x_PROGRAM_ID NUMBER,
167 x_PROGRAM_UPDATE_DATE DATE,
168 x_WH_UPDATE_DATE DATE,
169 x_STATUS VARCHAR2
170 ) IS
171 CURSOR C IS
172 SELECT *
173 FROM HZ_CERTIFICATIONS
174 WHERE rowid = x_Rowid
175 FOR UPDATE of CERTIFICATION_ID NOWAIT;
176 Recinfo C%ROWTYPE;
177 BEGIN
178 OPEN C;
179 FETCH C INTO Recinfo;
180 If (C%NOTFOUND) then
181 CLOSE C;
182 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
183 APP_EXCEPTION.RAISE_EXCEPTION;
184 End If;
185 CLOSE C;
186 if (
187 ( ( Recinfo.CERTIFICATION_ID = x_CERTIFICATION_ID)
188 OR ( ( Recinfo.CERTIFICATION_ID = NULL )
189 AND ( x_CERTIFICATION_ID = NULL )))
190 AND ( ( Recinfo.CERTIFICATION_NAME = x_CERTIFICATION_NAME)
191 OR ( ( Recinfo.CERTIFICATION_NAME = NULL )
192 AND ( x_CERTIFICATION_NAME = NULL )))
193 AND ( ( Recinfo.CURRENT_STATUS = x_CURRENT_STATUS)
194 OR ( ( Recinfo.CURRENT_STATUS = NULL )
195 AND ( x_CURRENT_STATUS = NULL )))
196 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
197 OR ( ( Recinfo.PARTY_ID = NULL )
198 AND ( x_PARTY_ID = NULL )))
199 AND ( ( Recinfo.EXPIRES_ON_DATE = x_EXPIRES_ON_DATE)
200 OR ( ( Recinfo.EXPIRES_ON_DATE = NULL )
201 AND ( x_EXPIRES_ON_DATE = NULL )))
202 AND ( ( Recinfo.GRADE = x_GRADE)
203 OR ( ( Recinfo.GRADE = NULL )
204 AND ( x_GRADE = NULL )))
205 AND ( ( Recinfo.ISSUED_BY_AUTHORITY = x_ISSUED_BY_AUTHORITY)
206 OR ( ( Recinfo.ISSUED_BY_AUTHORITY = NULL )
207 AND ( x_ISSUED_BY_AUTHORITY = NULL )))
208 AND ( ( Recinfo.ISSUED_ON_DATE = x_ISSUED_ON_DATE)
209 OR ( ( Recinfo.ISSUED_ON_DATE = NULL )
210 AND ( x_ISSUED_ON_DATE = NULL )))
211 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
212 OR ( ( Recinfo.CREATED_BY = NULL )
213 AND ( x_CREATED_BY = NULL )))
214 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
215 OR ( ( Recinfo.CREATION_DATE = NULL )
216 AND ( x_CREATION_DATE = NULL )))
217 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
218 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
219 AND ( x_LAST_UPDATE_LOGIN = NULL )))
220 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
221 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
222 AND ( x_LAST_UPDATE_DATE = NULL )))
223 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
224 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
225 AND ( x_LAST_UPDATED_BY = NULL )))
226 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
227 OR ( ( Recinfo.REQUEST_ID = NULL )
228 AND ( x_REQUEST_ID = NULL )))
229 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
230 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
231 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
232 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
233 OR ( ( Recinfo.PROGRAM_ID = NULL )
234 AND ( x_PROGRAM_ID = NULL )))
235 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
236 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
237 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
238 AND ( ( Recinfo.WH_UPDATE_DATE = x_WH_UPDATE_DATE)
239 OR ( ( Recinfo.WH_UPDATE_DATE = NULL )
240 AND ( x_WH_UPDATE_DATE = NULL )))
241
242
243 AND ( ( Recinfo.STATUS = x_STATUS)
244 OR ( ( Recinfo.STATUS = NULL )
245 AND ( x_STATUS= NULL )))
246 ) then
247 return;
248 else
249 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
250 APP_EXCEPTION.RAISE_EXCEPTION;
251 End If;
252 END Lock_Row;
253
254 END HZ_CERTIFICATIONS_PKG;