[Home] [Help]
PACKAGE BODY: APPS.CE_BA_SIGNATORIES_PKG
Source
1 PACKAGE BODY CE_BA_SIGNATORIES_PKG as
2 /* $Header: cebasigb.pls 120.0 2005/03/30 21:38:05 shawang noship $ */
3 --
4 -- Package
5 -- ce_ba_signatoriespkg
6 -- Purpose
7 -- To contain validation and insertion routines for ce_ba_signatories
8 -- History
9 -- 15-dec-2004 Shaik Vali Created
10
11 FUNCTION body_revision RETURN VARCHAR2 IS
12 BEGIN
13
14 RETURN '$Revision: 120.0 $';
15
16 END body_revision;
17
18 FUNCTION spec_revision RETURN VARCHAR2 IS
19 BEGIN
20
21 RETURN G_spec_revision;
22
23 END spec_revision;
24
25
26 --
27 -- Procedure
28 -- Insert_Row
29 -- Purpose
30 -- Inserts a row into ce_ba_signatories
31 -- History
32 -- 15-Dec-2004 Shaik Vali Created
33 -- Arguments
34 -- all the columns of the table CE_BA_SIGNATORIES
35 -- Example
36 -- CE_BA_SIGNATORIES_PKG.Insert_Row(....;
37 -- Notes
38 --
39 PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2,
40 X_Signatory_Id IN OUT NOCOPY NUMBER,
41 X_Bank_Account_Id NUMBER,
42 X_Person_Id NUMBER,
43 X_Single_limit_amount NUMBER,
44 X_Joint_limit_amount NUMBER,
45 X_Signer_group VARCHAR2,
46 X_Other_limits VARCHAR2,
47 X_Start_date DATE,
48 X_End_date DATE,
49 X_Status VARCHAR2,
50 X_Approval_type VARCHAR2,
51 X_requester_id NUMBER,
52 X_deleted_flag VARCHAR2,
53 X_Last_Updated_By NUMBER,
54 X_Last_Update_Date DATE,
55 X_Last_Update_Login NUMBER,
56 X_Created_By NUMBER,
57 X_Creation_Date DATE,
58 X_Attribute_category VARCHAR2,
59 X_Attribute1 VARCHAR2,
60 X_Attribute2 VARCHAR2,
61 X_Attribute3 VARCHAR2,
62 X_Attribute4 VARCHAR2,
63 X_Attribute5 VARCHAR2,
64 X_Attribute6 VARCHAR2,
65 X_Attribute7 VARCHAR2,
66 X_Attribute8 VARCHAR2,
67 X_Attribute9 VARCHAR2,
68 X_Attribute10 VARCHAR2,
69 X_Attribute11 VARCHAR2,
70 X_Attribute12 VARCHAR2,
71 X_Attribute13 VARCHAR2,
72 X_Attribute14 VARCHAR2,
73 X_Attribute15 VARCHAR2
74 ) IS
75 CURSOR C IS SELECT rowid FROM CE_BA_SIGNATORIES
76 WHERE signatory_id = X_signatory_id;
77
78 CURSOR C2 IS SELECT ce_ba_signatories_s.nextval FROM sys.dual;
79 --
80 BEGIN
81 -- cep_standard.debug('open c2 ');
82
83 OPEN C2;
84 FETCH C2 INTO X_Signatory_id;
85 CLOSE C2;
86 --
87
88 INSERT INTO CE_BA_SIGNATORIES(
89 signatory_id,
90 bank_account_id,
91 person_id,
92 single_limit_amount,
93 joint_limit_amount,
94 signer_group,
95 other_limits,
96 start_date,
97 end_date,
98 status,
99 approval_type_flag,
100 requester_id,
101 deleted_flag,
102 Last_Updated_By,
103 Last_Update_Date,
104 Last_Update_Login,
105 Created_By,
106 Creation_Date,
107 attribute_category,
108 attribute1,
109 attribute2,
110 attribute3,
111 attribute4,
112 attribute5,
113 attribute6,
114 attribute7,
115 attribute8,
116 attribute9,
117 attribute10,
118 attribute11,
119 attribute12,
120 attribute13,
121 attribute14,
122 attribute15
123 ) VALUES (
124 X_Signatory_Id,
125 X_Bank_Account_id,
126 X_Person_Id,
127 X_Single_limit_amount,
128 X_Joint_limit_amount,
129 X_Signer_group,
130 X_Other_limits,
131 X_Start_date,
132 X_End_date,
133 X_Status,
134 X_Approval_type,
135 X_requester_id,
136 X_deleted_flag,
137 X_Last_Updated_By ,
138 X_Last_Update_Date,
139 X_Last_Update_Login,
140 X_Created_By ,
141 X_Creation_Date,
142 X_Attribute_category,
143 X_Attribute1,
144 X_Attribute2,
145 X_Attribute3,
146 X_Attribute4,
147 X_Attribute5,
148 X_Attribute6,
149 X_Attribute7,
150 X_Attribute8,
151 X_Attribute9,
152 X_Attribute10,
153 X_Attribute11,
154 X_Attribute12,
155 X_Attribute13,
156 X_Attribute14,
157 X_Attribute15
158 );
159 --
160 OPEN C;
161 FETCH C INTO X_Rowid;
162 if (C%NOTFOUND) then
163 CLOSE C;
164 Raise NO_DATA_FOUND;
165 end if;
166 CLOSE C;
167 END Insert_Row;
168
169
170 --
171 -- Procedure
172 -- Lock_Row
173 -- Purpose
174 -- Locks a row into ce_ba_signatories
175 -- History
176 -- 15-Dec-2004 Shaik Vali Created
177 -- Arguments
178 -- all the columns of the table CE_BA_SIGNATORIES
179 -- Example
180 -- ce_ba_signatories_pkg.Lock_Row(....;
181 -- Notes
182 --
183 PROCEDURE Lock_Row(
184 X_Rowid IN OUT NOCOPY VARCHAR2,
185 X_Signatory_Id IN OUT NOCOPY NUMBER,
186 X_Bank_Account_Id NUMBER,
187 X_Person_Id NUMBER,
188 X_Single_limit_amount NUMBER,
189 X_Joint_limit_amount NUMBER,
190 X_Signer_group VARCHAR2,
191 X_Other_limits VARCHAR2,
192 X_Start_date DATE,
193 X_End_date DATE,
194 X_Status VARCHAR2,
195 X_Approval_type VARCHAR2,
196 X_Attribute_category VARCHAR2,
197 X_Attribute1 VARCHAR2,
198 X_Attribute2 VARCHAR2,
199 X_Attribute3 VARCHAR2,
200 X_Attribute4 VARCHAR2,
201 X_Attribute5 VARCHAR2,
202 X_Attribute6 VARCHAR2,
203 X_Attribute7 VARCHAR2,
204 X_Attribute8 VARCHAR2,
205 X_Attribute9 VARCHAR2,
206 X_Attribute10 VARCHAR2,
207 X_Attribute11 VARCHAR2,
208 X_Attribute12 VARCHAR2,
209 X_Attribute13 VARCHAR2,
210 X_Attribute14 VARCHAR2,
211 X_Attribute15 VARCHAR2
212 ) IS
213 CURSOR C IS
214 SELECT *
215 FROM CE_BA_SIGNATORIES
216 WHERE rowid = X_Rowid
217 FOR UPDATE of Signatory_id NOWAIT;
218 Recinfo C%ROWTYPE;
219 BEGIN
220 OPEN C;
221 FETCH C INTO Recinfo;
222 if (C%NOTFOUND) then
223 CLOSE C;
224 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
225 APP_EXCEPTION.Raise_Exception;
226 end if;
227 CLOSE C;
228 if (
229 (Recinfo.signatory_id = X_Signatory_id)
230 AND (Recinfo.bank_account_id = X_Bank_Account_Id)
231 AND (Recinfo.person_id = X_person_id)
232 AND (Recinfo.single_limit_amount = X_single_limit_amount
233 OR (Recinfo.single_limit_amount is NULL
234 AND (X_single_limit_amount is NULL)))
235 AND (Recinfo.joint_limit_amount = X_joint_limit_amount
236 OR (Recinfo.joint_limit_amount is NULL
237 AND X_joint_limit_amount is NULL))
238 AND (Recinfo.signer_group = X_signer_group
239 OR (Recinfo.signer_group is NULL
240 AND (X_signer_group is NULL)))
241 AND (Recinfo.other_limits = X_other_limits
242 OR (Recinfo.other_limits is NULL
243 AND (X_other_limits is NULL)))
244 AND (Recinfo.start_date = X_start_Date
245 OR (Recinfo.start_date is NULL
246 AND (X_start_date is NULL)))
247 AND (Recinfo.end_date = X_end_date
248 OR (Recinfo.end_date is NULL
249 AND (X_end_date is NULL)))
250 AND (Recinfo.status = X_status
251 OR (Recinfo.status is NULL
252 AND (X_status is NULL)))
253 AND (Recinfo.approval_type_flag = X_approval_type
254 OR (Recinfo.approval_type_flag is NULL
255 AND (X_approval_type is NULL)))
256 AND (Recinfo.attribute_category = X_attribute_category
257 OR (Recinfo.attribute_category is NULL
258 AND (X_attribute_category is NULL)))
259 AND (Recinfo.attribute1 = X_attribute1
260 OR (Recinfo.attribute1 is NULL
261 AND (X_attribute1 is NULL)))
262 AND (Recinfo.attribute2 = X_attribute2
263 OR (Recinfo.attribute2 is NULL
264 AND (X_attribute2 is NULL)))
265 AND (Recinfo.attribute3 = X_attribute3
266 OR (Recinfo.attribute3 is NULL
267 AND (X_attribute3 is NULL)))
268 AND (Recinfo.attribute4 = X_attribute4
269 OR (Recinfo.attribute4 is NULL
270 AND (X_attribute4 is NULL)))
271 AND (Recinfo.attribute5 = X_attribute5
272 OR (Recinfo.attribute5 is NULL
273 AND (X_attribute5 is NULL)))
274 AND (Recinfo.attribute6 = X_attribute6
275 OR (Recinfo.attribute6 is NULL
276 AND (X_attribute6 is NULL)))
277 AND (Recinfo.attribute7 = X_attribute7
278 OR (Recinfo.attribute7 is NULL
279 AND (X_attribute7 is NULL)))
280 AND (Recinfo.attribute8 = X_attribute8
281 OR (Recinfo.attribute8 is NULL
282 AND (X_attribute8 is NULL)))
283 AND (Recinfo.attribute9 = X_attribute9
284 OR (Recinfo.attribute9 is NULL
285 AND (X_attribute9 is NULL)))
286 AND (Recinfo.attribute10 = X_attribute10
287 OR (Recinfo.attribute10 is NULL
288 AND (X_attribute10 is NULL)))
289 AND (Recinfo.attribute11 = X_attribute11
290 OR (Recinfo.attribute11 is NULL
291 AND (X_attribute11 is NULL)))
292 AND (Recinfo.attribute12 = X_attribute12
293 OR (Recinfo.attribute12 is NULL
294 AND (X_attribute12 is NULL)))
295 AND (Recinfo.attribute13 = X_attribute13
296 OR (Recinfo.attribute13 is NULL
297 AND (X_attribute13 is NULL)))
298 AND (Recinfo.attribute14 = X_attribute14
299 OR (Recinfo.attribute14 is NULL
300 AND (X_attribute14 is NULL)))
301 AND (Recinfo.attribute15 = X_attribute15
302 OR (Recinfo.attribute15 is NULL
303 AND (X_attribute15 is NULL)))
304
305 ) then
306 return;
307 else
308 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
309 APP_EXCEPTION.Raise_Exception;
310 end if;
311 END Lock_Row;
312
313 --
314 -- Procedure
315 -- Update_Row
316 -- Purpose
317 -- Updates a row into ce_ba_signatories
318 -- History
319 -- 15-Dec-2004 Shaik Vali Created
320 -- Arguments
321 -- all the columns of the table CE_BA_SIGNATORIES
322 -- Example
323 -- ce_ba_signatories_pkg.Update_Row(....;
324 -- Notes
325 --
326 PROCEDURE Update_Row( X_Rowid IN OUT NOCOPY VARCHAR2,
327 X_Signatory_Id IN OUT NOCOPY NUMBER,
328 X_Bank_Account_Id NUMBER,
329 X_Person_Id NUMBER,
330 X_Single_limit_amount NUMBER,
331 X_Joint_limit_amount NUMBER,
332 X_Signer_group VARCHAR2,
333 X_Other_limits VARCHAR2,
334 X_Start_date DATE,
335 X_End_date DATE,
336 X_Status VARCHAR2,
337 X_Approval_type VARCHAR2,
338 X_requester_id NUMBER,
339 X_Last_Updated_By NUMBER,
340 X_Last_Update_Date DATE,
341 X_Last_Update_Login NUMBER,
342 X_Attribute_category VARCHAR2,
343 X_Attribute1 VARCHAR2,
344 X_Attribute2 VARCHAR2,
345 X_Attribute3 VARCHAR2,
346 X_Attribute4 VARCHAR2,
347 X_Attribute5 VARCHAR2,
348 X_Attribute6 VARCHAR2,
349 X_Attribute7 VARCHAR2,
350 X_Attribute8 VARCHAR2,
351 X_Attribute9 VARCHAR2,
352 X_Attribute10 VARCHAR2,
353 X_Attribute11 VARCHAR2,
354 X_Attribute12 VARCHAR2,
355 X_Attribute13 VARCHAR2,
356 X_Attribute14 VARCHAR2,
357 X_Attribute15 VARCHAR2
358 ) IS
359 BEGIN
360 UPDATE CE_BA_SIGNATORIES
361 SET
362 signatory_id = X_signatory_id,
363 bank_account_id = X_Bank_Account_Id,
364 person_id = X_person_id,
365 single_limit_amount = X_single_limit_amount,
366 joint_limit_amount = X_joint_limit_amount,
367 signer_group = X_signer_group,
368 other_limits = X_other_limits,
369 start_date = X_start_date,
370 end_date = X_end_date,
371 status = X_status,
372 approval_type_flag = X_approval_type,
373 requester_id = X_requester_id,
374 last_updated_by = X_Last_Updated_By,
375 last_update_date = X_Last_Update_Date,
376 last_update_login = X_Last_Update_Login,
377 attribute_category = X_attribute_category,
378 attribute1 = X_attribute1,
379 attribute2 = X_attribute2,
380 attribute3 = X_attribute3,
381 attribute4 = X_attribute4,
382 attribute5 = X_attribute5,
383 attribute6 = X_attribute6,
384 attribute7 = X_attribute7,
385 attribute8 = X_attribute8,
386 attribute9 = X_attribute9,
387 attribute10 = X_attribute10,
388 attribute11 = X_attribute11,
389 attribute12 = X_attribute12,
390 attribute13 = X_attribute13,
391 attribute14 = X_attribute14,
392 attribute15 = X_attribute15
393 WHERE rowid = X_Rowid;
394 --
395 if (SQL%NOTFOUND) then
396 Raise NO_DATA_FOUND;
397 end if;
398 END Update_Row;
399 --
400
401 -- Procedure
402 -- Delete_Row
403 -- Purpose
404 -- Deletes a row from ce_je_mappings
405 -- History
406 -- 14-JAN-2005 Shaik Vali Created
407 -- Arguments
408 -- x_rowid Rowid of a row
409 -- Example
410 -- ce_ba_signatories_pkg.delete_row(...;
411 -- Notes
412 --
413 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
414 BEGIN
415 -- Do not delete the record from the table.
416 -- rather mark it for delete.
417
418 UPDATE ce_ba_signatories
419 SET deleted_flag = 'Y'
420 WHERE rowid=X_Rowid;
421 --
422 if (SQL%NOTFOUND) then
423 Raise NO_DATA_FOUND;
424 end if;
425 END Delete_Row;
426 --
427 END CE_BA_SIGNATORIES_PKG;