[Home] [Help]
PACKAGE BODY: APPS.PV_PRGM_BENEFITS_PKG
Source
1 PACKAGE BODY PV_PRGM_BENEFITS_PKG as
2 /* $Header: pvxtppbb.pls 115.9 2003/11/07 06:13:54 ktsao ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_PROGRAM_BENEFITS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 28-FEB-2002 Jessica.Lee Created
11 -- 1-APR-2002 Peter.Nixon Modified
12 -- Changed benefit_id NUMBER to benefit_code VARCHAR2
13 -- 24-SEP-2003 Karen.Tsao Modified for 11.5.10
14 -- 02-OCT-2003 Karen.Tsao Modified for new column responsibility_id
15 -- 06-NOV-2003 Karen.Tsao Took out column responsibility_id
16 -- NOTE
17 --
18 -- End of Comments
19 -- ===============================================================
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_PROGRAM_BENEFITS_PKG';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxtpbbb.pls';
23
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
38 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
39 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
40
41 PROCEDURE Insert_Row(
42 px_program_benefits_id IN OUT NOCOPY NUMBER
43 ,p_program_id NUMBER
44 ,p_benefit_code VARCHAR2
45 ,p_benefit_id NUMBER
46 ,p_benefit_type_code VARCHAR2
47 ,p_delete_flag VARCHAR2
48 ,p_last_update_login NUMBER
49 ,p_last_update_date DATE
50 ,p_last_updated_by NUMBER
51 ,p_created_by NUMBER
52 ,p_creation_date DATE
53 ,p_object_version_number NUMBER
54 )
55
56 IS
57
58 BEGIN
59
60 INSERT INTO PV_PROGRAM_BENEFITS(
61 program_benefits_id
62 ,program_id
63 ,benefit_code
64 ,benefit_id
65 ,benefit_type_code
66 ,delete_flag
67 ,last_update_login
68 ,last_update_date
69 ,last_updated_by
70 ,created_by
71 ,creation_date
72 ,object_version_number
73 ) VALUES (
74 -- DECODE( px_program_benefits_id, FND_API.g_miss_num, NULL, px_program_benefits_id)
75 -- ,DECODE( p_program_id, FND_API.g_miss_num, NULL, p_program_id)
76 -- ,DECODE( p_benefit_code, FND_API.g_miss_char, NULL, p_benefit_code)
77 -- ,DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
78 -- ,DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
79 -- ,DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
80 -- ,DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by)
81 -- ,DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date)
82 -- ,DECODE( p_object_version_number, FND_API.g_miss_num, NULL, p_object_version_number)
83
84 DECODE( px_program_benefits_id, NULL,px_program_benefits_id, FND_API.g_miss_num, NULL, px_program_benefits_id)
85 ,DECODE( p_program_id, NULL,p_program_id, FND_API.g_miss_num, NULL, p_program_id)
86 ,DECODE( p_benefit_code, NULL,p_benefit_code, FND_API.g_miss_char, NULL, p_benefit_code)
87 ,DECODE( p_benefit_id, NULL,p_benefit_id, FND_API.g_miss_num, NULL, p_benefit_id)
88 ,DECODE( p_benefit_type_code, NULL,p_benefit_type_code, FND_API.g_miss_char, NULL, p_benefit_type_code)
89 ,DECODE( p_delete_flag, NULL,p_delete_flag, FND_API.g_miss_char, NULL, p_delete_flag)
90 ,DECODE( p_last_update_login, NULL, p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
91 ,DECODE( p_last_update_date, NULL, p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
92 ,DECODE( p_last_updated_by, NULL, p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
93 ,DECODE( p_created_by, NULL, p_created_by, FND_API.g_miss_num, NULL, p_created_by)
94 ,DECODE( p_creation_date, NULL, p_creation_date, FND_API.g_miss_date, NULL, p_creation_date)
95 ,DECODE( p_object_version_number, NULL, p_object_version_number, FND_API.g_miss_num, NULL, p_object_version_number)
96
97 );
98 END Insert_Row;
99
100
101 -- ========================================================
102 --
103 -- NAME
104 -- createUpdateBody
105 --
106 -- PURPOSE
107 --
108 -- NOTES
109 --
110 -- HISTORY
111 --
112 -- ========================================================
113 PROCEDURE Update_Row(
114 p_program_benefits_id NUMBER
115 ,p_program_id NUMBER
116 ,p_benefit_code VARCHAR2
117 ,p_benefit_id NUMBER
118 ,p_benefit_type_code VARCHAR2
119 ,p_delete_flag VARCHAR2
120 ,p_last_update_login NUMBER
121 ,p_object_version_number NUMBER
122 ,p_last_update_date DATE
123 ,p_last_updated_by NUMBER
124 )
125
126 IS
127 BEGIN
128 Update PV_PROGRAM_BENEFITS
129 SET
130 -- program_benefits_id = DECODE( p_program_benefits_id, FND_API.g_miss_num, program_benefits_id, p_program_benefits_id)
131 -- ,program_id = DECODE( p_program_id, FND_API.g_miss_num, program_id, p_program_id)
132 -- ,benefit_code = DECODE( p_benefit_code, FND_API.g_miss_char, benefit_code, p_benefit_code)
133 -- ,last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login)
134 -- ,object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number+1)
135 -- ,last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date)
136 -- ,last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by)
137 program_benefits_id = DECODE( p_program_benefits_id, NULL, program_benefits_id, FND_API.g_miss_num, NULL, p_program_benefits_id)
138 ,program_id = DECODE( p_program_id, NULL, program_id, FND_API.g_miss_num, NULL, p_program_id)
139 ,benefit_code = DECODE( p_benefit_code, NULL, benefit_code, FND_API.g_miss_char, NULL, p_benefit_code)
140 ,benefit_id = DECODE( p_benefit_id, NULL, benefit_id, FND_API.g_miss_num, NULL, p_benefit_id)
141 ,benefit_type_code = DECODE( p_benefit_type_code, NULL, benefit_type_code, FND_API.g_miss_char, NULL, p_benefit_type_code)
142 ,delete_flag = DECODE( p_delete_flag, NULL, delete_flag, FND_API.g_miss_char, NULL, p_delete_flag)
143 ,last_update_login = DECODE( p_last_update_login, NULL, last_update_login, FND_API.g_miss_num, NULL, p_last_update_login)
144 ,object_version_number = DECODE( p_object_version_number, NULL, object_version_number, FND_API.g_miss_num, NULL, p_object_version_number+1)
145 ,last_update_date = DECODE( p_last_update_date, NULL, last_update_date, FND_API.g_miss_date, NULL, p_last_update_date)
146 ,last_updated_by = DECODE( p_last_updated_by, NULL, last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by)
147
148 WHERE PROGRAM_BENEFITS_ID = p_program_benefits_id
149 AND object_version_number = p_object_version_number;
150
151 IF (SQL%NOTFOUND) THEN
152 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
153 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
154 FND_MSG_PUB.add;
155 END IF;
156 RAISE FND_API.g_exc_error;
157 END IF;
158
159 END Update_Row;
160
161
162 -- ========================================================
163 --
164 -- NAME
165 -- Delete_Row
166 --
167 -- PURPOSE
168 --
169 -- NOTES
170 --
171 -- HISTORY
172 --
173 -- ========================================================
174 PROCEDURE Delete_Row(
175 p_program_benefits_id NUMBER
176 ,p_object_version_number NUMBER
177 )
178 IS
179 BEGIN
180 UPDATE PV_PROGRAM_BENEFITS
181 SET
182 --program_benefits_id = DECODE( p_program_benefits_id, NULL ,program_benefits_id, FND_API.g_miss_num, NULL , p_program_benefits_id)
183 delete_flag ='Y'
184 ,last_update_date = SYSDATE
185 ,last_updated_by = FND_GLOBAL.user_id
186 ,last_update_login = FND_GLOBAL.conc_login_id
187 ,object_version_number = DECODE( p_object_version_number, NULL ,object_version_number, FND_API.g_miss_num, NULL , p_object_version_number+1)
188
189 WHERE program_benefits_id = p_program_benefits_id
190 AND object_version_number = p_object_version_number;
191
192 IF (SQL%NOTFOUND) THEN
193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
194 FND_MESSAGE.set_name('PV', 'PV_RECORD_NOT_FOUND');
195 FND_MSG_PUB.add;
196 END IF;
197 RAISE FND_API.g_exc_error;
198 END IF;
199
200 END Delete_Row ;
201
202 -- ========================================================
203 --
204 -- NAME
205 -- Lock_Row
206 --
207 -- PURPOSE
208 --
209 -- NOTES
210 --
211 -- HISTORY
212 --
213 -- ========================================================
214 PROCEDURE Lock_Row(
215 px_program_benefits_id IN OUT NOCOPY NUMBER
216 ,p_program_id NUMBER
217 ,p_benefit_code VARCHAR2
218 ,p_benefit_id NUMBER
219 ,p_benefit_type_code VARCHAR2
220 ,p_delete_flag VARCHAR2
221 ,p_last_update_login NUMBER
222 ,px_object_version_number IN OUT NOCOPY NUMBER
223 ,p_last_update_date DATE
224 ,p_last_updated_by NUMBER
225 ,p_created_by NUMBER
226 ,p_creation_date DATE
227 )
228
229 IS
230 CURSOR C IS
231 SELECT *
232 FROM PV_PROGRAM_BENEFITS
233 WHERE PROGRAM_BENEFITS_ID = px_program_benefits_id
234 FOR UPDATE of PROGRAM_BENEFITS_ID NOWAIT;
235 Recinfo C%ROWTYPE;
236
237 BEGIN
238 OPEN c;
239 FETCH c INTO Recinfo;
240 If (c%NOTFOUND) then
241 CLOSE c;
242 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 END IF;
245 CLOSE C;
246 IF (
247 ( Recinfo.program_benefits_id = px_program_benefits_id)
248 AND ( ( Recinfo.program_id = p_program_id)
249 OR ( ( Recinfo.program_id IS NULL )
250 AND ( p_program_id IS NULL )))
251 AND ( ( Recinfo.benefit_code = p_benefit_code)
252 OR ( ( Recinfo.benefit_code IS NULL )
253 AND ( p_benefit_code IS NULL )))
254 AND ( ( Recinfo.benefit_id = p_benefit_id)
255 OR ( ( Recinfo.benefit_id IS NULL )
256 AND ( p_benefit_id IS NULL )))
257 AND ( ( Recinfo.benefit_type_code = p_benefit_type_code)
258 OR ( ( Recinfo.benefit_type_code IS NULL )
259 AND ( p_benefit_type_code IS NULL )))
260 AND ( ( Recinfo.delete_flag = p_delete_flag)
261 OR ( ( Recinfo.delete_flag IS NULL )
262 AND ( p_delete_flag IS NULL )))
263 AND ( ( Recinfo.last_update_login = p_last_update_login)
264 OR ( ( Recinfo.last_update_login IS NULL )
265 AND ( p_last_update_login IS NULL )))
266 AND ( ( Recinfo.object_version_number = px_object_version_number)
267 OR ( ( Recinfo.object_version_number IS NULL )
268 AND ( px_object_version_number IS NULL )))
269 AND ( ( Recinfo.last_update_date = p_last_update_date)
270 OR ( ( Recinfo.last_update_date IS NULL )
271 AND ( p_last_update_date IS NULL )))
272 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
273 OR ( ( Recinfo.last_updated_by IS NULL )
274 AND ( p_last_updated_by IS NULL )))
275 AND ( ( Recinfo.created_by = p_created_by)
276 OR ( ( Recinfo.created_by IS NULL )
277 AND ( p_created_by IS NULL )))
278 AND ( ( Recinfo.creation_date = p_creation_date)
279 OR ( ( Recinfo.creation_date IS NULL )
280 AND ( p_creation_date IS NULL )))
281 ) THEN
282 RETURN;
283 ELSE
284 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
285 APP_EXCEPTION.RAISE_EXCEPTION;
286 END IF;
287 END Lock_Row;
288
289 END PV_PRGM_BENEFITS_PKG;