[Home] [Help]
PACKAGE BODY: APPS.HZ_CREDIT_PROFILE_AMTS_PKG
Source
1 PACKAGE BODY HZ_CREDIT_PROFILE_AMTS_PKG AS
2 /* $Header: ARHCRPAB.pls 115.3 2003/08/16 02:00:47 rajkrish ship $ */
3
4 ---------------------------
5 -- PROCEDURES AND FUNCTIONS
6 ---------------------------
7
8 --========================================================================
9 -- PROCEDURE : Insert_row PUBLIC
10 -- PARAMETERS: p_row_id ROWID of the current record in
11 -- table HZ_CREDIT_PROFILE_AMTS
12 -- p_credit_profile_amt_id primary key
13 -- p_credit_profile_id credit_profile_id
14 -- p_currency_code currency_code
15 -- p_trx_credit_limit trx_credit_limit
16 -- p_overall_credit_limit overall_credit_limit
17 -- p_creation_date date, when a record was inserted
18 -- p_created_by userid of the person,who inserted
19 -- a record
20 -- p_last_update_date
21 -- p_last_updated_by
22 -- p_last_update_login
23 --
24 -- COMMENT : Procedure inserts record into the table HZ_CREDIT_PROFILE_AMTS
25 --
26 --========================================================================
27 PROCEDURE Insert_row
28 ( p_row_id OUT NOCOPY VARCHAR2
29 , p_credit_profile_amt_id NUMBER
30 , p_credit_profile_id NUMBER
31 , p_currency_code VARCHAR2
32 , p_trx_credit_limit NUMBER
33 , p_overall_credit_limit NUMBER
34 , p_creation_date DATE
35 , p_created_by NUMBER
36 , p_last_update_date DATE
37 , p_last_updated_by NUMBER
38 , p_last_update_login NUMBER
39 , p_attribute_category VARCHAR2
40 , p_attribute1 VARCHAR2
41 , p_attribute2 VARCHAR2
42 , p_attribute3 VARCHAR2
43 , p_attribute4 VARCHAR2
44 , p_attribute5 VARCHAR2
45 , p_attribute6 VARCHAR2
46 , p_attribute7 VARCHAR2
47 , p_attribute8 VARCHAR2
48 , p_attribute9 VARCHAR2
49 , p_attribute10 VARCHAR2
50 , p_attribute11 VARCHAR2
51 , p_attribute12 VARCHAR2
52 , p_attribute13 VARCHAR2
53 , p_attribute14 VARCHAR2
54 , p_attribute15 VARCHAR2
55 )
56 IS
57
58 CURSOR prof_amt_csr IS
59 SELECT
60 rowid
61 FROM
62 hz_credit_profile_amts
63 WHERE credit_profile_amt_id=p_credit_profile_amt_id;
64
65 BEGIN
66
67 INSERT INTO hz_credit_profile_amts
68 ( credit_profile_amt_id
69 , credit_profile_id
70 , creation_date
71 , created_by
72 , last_update_date
73 , last_updated_by
74 , last_update_login
75 , program_application_id
76 , program_id
77 , program_update_date
78 , request_id
79 , currency_code
80 , trx_credit_limit
81 , overall_credit_limit
82 , attribute_category
83 , attribute1
84 , attribute2
85 , attribute3
86 , attribute4
87 , attribute5
88 , attribute6
89 , attribute7
90 , attribute8
91 , attribute9
92 , attribute10
93 , attribute11
94 , attribute12
95 , attribute13
96 , attribute14
97 , attribute15
98 )
99 VALUES
100 ( p_credit_profile_amt_id
101 , p_credit_profile_id
102 , p_creation_date
103 , p_created_by
104 , p_last_update_date
105 , p_last_updated_by
106 , p_last_update_login
107 , null
108 , null
109 , null
110 , null
111 , p_currency_code
112 , p_trx_credit_limit
113 , p_overall_credit_limit
114 , p_attribute_category
115 , p_attribute1
116 , p_attribute2
117 , p_attribute3
118 , p_attribute4
119 , p_attribute5
120 , p_attribute6
121 , p_attribute7
122 , p_attribute8
123 , p_attribute9
124 , p_attribute10
125 , p_attribute11
126 , p_attribute12
127 , p_attribute13
128 , p_attribute14
129 , p_attribute15
130 );
131
132 OPEN prof_amt_csr;
133 FETCH prof_amt_csr INTO p_row_id;
134 IF (prof_amt_csr%NOTFOUND)
135 THEN
136 CLOSE prof_amt_csr;
137 RAISE NO_DATA_FOUND;
138 END IF;
139 CLOSE prof_amt_csr;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
144 THEN
145 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Insert_row');
146 END IF;
147 RAISE;
148
149 END Insert_row;
150
151 --========================================================================
152 -- PROCEDURE : Lock_row PUBLIC
153 -- PARAMETERS: p_row_id rowid
154 -- p_currency_code currency-code
155 -- p_trx_credit_limit trx_credit_limit
156 -- p_overall_credit_limit overall_credit_limit
157 -- p_last_update_date
158 -- COMMENT : Procedure locks record in the table HZ_CREDIT_PROFILE_AMTS
159 --
160 --========================================================================
161 PROCEDURE Lock_row
162 ( p_row_id VARCHAR2
163 , p_currency_code VARCHAR2
164 , p_trx_credit_limit NUMBER
165 , p_overall_credit_limit NUMBER
166 , p_last_update_date DATE
167 )
168 IS
169 CURSOR prof_csr
170 IS
171 SELECT *
172 FROM hz_credit_profile_amts
173 WHERE ROWID=CHARTOROWID(p_row_id)
174 FOR UPDATE OF last_update_date NOWAIT;
175
176 recinfo prof_csr%ROWTYPE;
177
178 BEGIN
179
180 OPEN prof_csr;
181 FETCH prof_csr INTO recinfo;
182 IF (prof_csr%NOTFOUND)
183 THEN
184 CLOSE prof_csr;
185 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_DELETED');
186 RAISE NO_DATA_FOUND;
187 END IF;
188 CLOSE prof_csr;
189
190 IF
191 ((recinfo.currency_code=p_currency_code)
192 OR (recinfo.currency_code is NULL AND p_currency_code is NULL))
193 AND
194 ((recinfo.trx_credit_limit=p_trx_credit_limit)
195 OR (recinfo.trx_credit_limit is NULL AND p_trx_credit_limit is NULL))
196 AND
197 ((recinfo.overall_credit_limit=p_overall_credit_limit)
198 OR (recinfo.overall_credit_limit is NULL AND p_overall_credit_limit is NULL))
199
200 THEN
201 NULL;
202 ELSE
203 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
204 APP_EXCEPTION.raise_exception;
205 END IF;
206
207 END Lock_Row;
208
209
210 --========================================================================
211 -- PROCEDURE : Update_row PUBLIC
212 -- PARAMETERS: p_row_id rowid
213 -- p_credit_profile_amt_id primary key
214 -- p_credit_profile_id credit_profile_id
215 -- p_currency_code currency_code
216 -- p_trx_credit_limit trx_credit_limit
217 -- p_overall_credit_limit overall_credit_limit credit_rating
218 -- p_last_update_date date, when record was updated
219 -- p_last_updated_by userid of the person,who updated the record
220 -- COMMENT : Procedure updates record in the table HZ_CREDIT_PROFILE_AMTS
221 --
222 --========================================================================
223 PROCEDURE Update_row
224 ( p_row_id VARCHAR2
225 , p_credit_profile_amt_id NUMBER
226 , p_credit_profile_id NUMBER
227 , p_currency_code VARCHAR2
228 , p_trx_credit_limit NUMBER
229 , p_overall_credit_limit NUMBER
230 , p_last_update_date DATE
231 , p_last_updated_by NUMBER
232 , p_attribute_category VARCHAR2
233 , p_attribute1 VARCHAR2
234 , p_attribute2 VARCHAR2
235 , p_attribute3 VARCHAR2
236 , p_attribute4 VARCHAR2
237 , p_attribute5 VARCHAR2
238 , p_attribute6 VARCHAR2
239 , p_attribute7 VARCHAR2
240 , p_attribute8 VARCHAR2
241 , p_attribute9 VARCHAR2
242 , p_attribute10 VARCHAR2
243 , p_attribute11 VARCHAR2
244 , p_attribute12 VARCHAR2
245 , p_attribute13 VARCHAR2
246 , p_attribute14 VARCHAR2
247 , p_attribute15 VARCHAR2
248 )
249 IS
250
251 BEGIN
252 UPDATE hz_credit_profile_amts
253 SET
254 credit_profile_id =p_credit_profile_id
255 , currency_code=p_currency_code
256 , trx_credit_limit=p_trx_credit_limit
257 , overall_credit_limit=p_overall_credit_limit
258 , last_update_date=p_last_update_date
259 , last_updated_by=p_last_updated_by
260 , attribute_category =p_attribute_category
261 , attribute1= p_attribute1
262 , attribute2= p_attribute2
263 , attribute3= p_attribute3
264 , attribute4= p_attribute4
265 , attribute5= p_attribute5
266 , attribute6= p_attribute6
267 , attribute7= p_attribute7
268 , attribute8= p_attribute8
269 , attribute9= p_attribute9
270 , attribute10= p_attribute10
271 , attribute11= p_attribute11
272 , attribute12= p_attribute12
273 , attribute13= p_attribute13
274 , attribute14= p_attribute14
275 , attribute15= p_attribute15
276 WHERE ROWID=CHARTOROWID(p_row_id);
277
278 IF (SQL%NOTFOUND)
279 THEN
280 RAISE NO_DATA_FOUND;
281 END IF;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
286 THEN
287 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Update_row');
288 END IF;
289 RAISE;
290
291 END Update_Row;
292
293 --========================================================================
294 -- PROCEDURE : Delete_row PUBLIC
295 -- PARAMETERS: p_row_id rowid
296 --
297 -- COMMENT : Procedure deletes record from the table HZ_CREDIT_PROFILE_AMTS
298 --
299 --========================================================================
300 PROCEDURE Delete_row
301 ( p_row_id VARCHAR2
302 )
303 IS
304 BEGIN
305 DELETE
306 FROM HZ_CREDIT_PROFILE_AMTS
307 WHERE ROWID=CHARTOROWID(p_row_id);
308
309 IF (SQL%NOTFOUND)
310 THEN
311 RAISE NO_DATA_FOUND;
312 END IF;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
317 THEN
318 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
319 END IF;
320 RAISE;
321
322 END Delete_row;
323
324 --========================================================================
325 -- PROCEDURE : Delete_rows PUBLIC
326 -- PARAMETERS: p_credit_profile_id credit_profile_id
327 --
328 -- COMMENT : Procedure deletes record from the table HZ_CREDIT_PROFILE_AMTS
329 -- when master record is deleted from HZ_CREDIT_PROFILES table
330 --========================================================================
331 PROCEDURE Delete_rows
332 ( p_credit_profile_id NUMBER
333 )
334 IS
335 BEGIN
336 DELETE
337 FROM HZ_CREDIT_PROFILE_AMTS
338 WHERE credit_profile_id=p_credit_profile_id;
339
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
344 THEN
345 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
346 END IF;
347 RAISE;
348
349 END Delete_rows;
350
351
352 END HZ_CREDIT_PROFILE_AMTS_PKG;