[Home] [Help]
PACKAGE BODY: APPS.HZ_CREDIT_PROFILES_PKG
Source
1 PACKAGE BODY HZ_CREDIT_PROFILES_PKG AS
2 /* $Header: ARHCRPRB.pls 115.3 2003/08/18 17:45:10 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_PROFILES
12 -- p_credit_profile_id primary key
13 -- p_organization_id operating unit id
14 -- p_item_category_id item_category_id
15 -- p_enable_flag YES/NO enable flag
16 -- p_effective_date_from effective_date_from
17 -- p_effective_date_to effective_date_to
18 -- p_credit_checking credit_checking
19 -- p_next_credit_review_date next_credit_review_date
20 -- p_tolerance tolerance
21 -- p_credit_hold credit_hold
22 -- p_credit_rating credit_rating
23 -- p_creation_date date, when a record was inserted
24 -- p_created_by userid of the person,who inserted
25 -- a record
26 --
27 -- COMMENT : Procedure inserts record into the table HZ_CREDIT_PROFILES
28 --
29 --========================================================================
30 PROCEDURE Insert_row
31 ( p_row_id OUT NOCOPY VARCHAR2
32 , p_credit_profile_id NUMBER
33 , p_organization_id NUMBER
34 , p_item_category_id NUMBER
35 , p_enable_flag VARCHAR2
36 , p_effective_date_from DATE
37 , p_effective_date_to DATE
38 , p_credit_checking VARCHAR2
39 , p_next_credit_review_date DATE
40 , p_tolerance NUMBER
41 , p_credit_hold VARCHAR2
42 , p_credit_rating VARCHAR2
43 , p_creation_date DATE
44 , p_created_by NUMBER
45 , p_last_update_date DATE
46 , p_last_updated_by NUMBER
47 , p_last_update_login NUMBER
48 )
49 IS
50
51 CURSOR profile_csr IS
52 SELECT
53 rowid
54 FROM
55 hz_credit_profiles
56 WHERE credit_profile_id=p_credit_profile_id;
57
58 BEGIN
59
60 INSERT INTO hz_credit_profiles
61 ( credit_profile_id
62 , organization_id
63 , item_category_id
64 , creation_date
65 , created_by
66 , last_update_date
67 , last_updated_by
68 , last_update_login
69 , enable_flag
70 , effective_date_from
71 , effective_date_to
72 , credit_checking
73 , next_credit_review_date
74 , tolerance
75 , credit_hold
76 , credit_rating
77 , program_application_id
78 , program_id
79 , program_update_date
80 , request_id
81 , attribute_category
82 , attribute1
83 , attribute2
84 , attribute3
85 , attribute4
86 , attribute5
87 , attribute6
88 , attribute7
89 , attribute8
90 , attribute9
91 , attribute10
92 , attribute11
93 , attribute12
94 , attribute13
95 , attribute14
96 , attribute15
97 )
98 VALUES
99 ( p_credit_profile_id
100 , p_organization_id
101 , p_item_category_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 , p_enable_flag
108 , p_effective_date_from
109 , p_effective_date_to
110 , p_credit_checking
111 , p_next_credit_review_date
112 , p_tolerance
113 , p_credit_hold
114 , p_credit_rating
115 , null
116 , null
117 , null
118 , null
119 , null
120 , null
121 , null
122 , null
123 , null
124 , null
125 , null
126 , null
127 , null
128 , null
129 , null
130 , null
131 , null
132 , null
133 , null
134 , null
135 );
136
137 OPEN profile_csr;
138 FETCH profile_csr INTO p_row_id;
139 IF (profile_csr%NOTFOUND)
140 THEN
141 CLOSE profile_csr;
142 RAISE NO_DATA_FOUND;
143 END IF;
144 CLOSE profile_csr;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
149 THEN
150 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Insert_row');
151 END IF;
152 RAISE;
153
154 END Insert_row;
155
156 --========================================================================
157 -- PROCEDURE : Lock_row PUBLIC
158 -- PARAMETERS: p_credit_profile_id credit_profile_id
159 -- p_last_update_date
160 -- COMMENT : Procedure locks record in the table HZ_CREDIT_PROFILES
161 --
162 --========================================================================
163 PROCEDURE Lock_row
164 ( p_credit_profile_id NUMBER
165 , p_last_update_date DATE
166 )
167 IS
168 CURSOR prof_csr
169 IS
170 SELECT *
171 FROM hz_credit_profiles
172 WHERE credit_profile_id=p_credit_profile_id
173 FOR UPDATE OF last_update_date NOWAIT;
174
175 recinfo prof_csr%ROWTYPE;
176
177 BEGIN
178
179 OPEN prof_csr;
180 FETCH prof_csr INTO recinfo;
181 IF (prof_csr%NOTFOUND)
182 THEN
183 CLOSE prof_csr;
184 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_DELETED');
185 RAISE NO_DATA_FOUND;
186 END IF;
187 CLOSE prof_csr;
188
189 IF
190 (recinfo.last_update_date=p_last_update_date)
191 THEN
192 NULL;
193 ELSE
194 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
195 APP_EXCEPTION.raise_exception;
196 END IF;
197
198 END Lock_Row;
199
200
201 --========================================================================
202 -- PROCEDURE : Update_row PUBLIC
203 -- PARAMETERS: p_credit_profile_id credit_profile_id
204 -- p_organization_id operating unit id
205 -- p_item_category_id item_category_id
206 -- p_enable_flag YES/NO enable flag
207 -- p_effective_date_from effective_date_from
208 -- p_effective_date_to effective_date_to
209 -- p_credit_checking credit_checking
210 -- p_next_credit_review_date next_credit_review_date
211 -- p_tolerance tolerance
212 -- p_credit_hold credit_hold
213 -- p_credit_rating credit_rating
214 -- p_last_update_date date, when record was updated
215 -- p_last_updated_by userid of the person,who updated the record
216 -- COMMENT : Procedure updates record in the table HZ_CREDIT_PROFILES
217 --
218 --========================================================================
219 PROCEDURE Update_row
220 ( p_credit_profile_id NUMBER
221 , p_organization_id NUMBER
222 , p_item_category_id NUMBER
223 , p_enable_flag VARCHAR2
224 , p_effective_date_from DATE
225 , p_effective_date_to DATE
226 , p_credit_checking VARCHAR2
227 , p_next_credit_review_date DATE
228 , p_tolerance NUMBER
229 , p_credit_hold VARCHAR2
230 , p_credit_rating VARCHAR2
231 , p_last_update_date DATE
232 , p_last_updated_by NUMBER
233 )
234 IS
235
236 BEGIN
237 UPDATE hz_credit_profiles
238 SET
239 organization_id =p_organization_id
240 , item_category_id=p_item_category_id
241 , enable_flag=p_enable_flag
242 , effective_date_from=p_effective_date_from
243 , effective_date_to=p_effective_date_to
244 , credit_checking=p_credit_checking
245 , next_credit_review_date=p_next_credit_review_date
246 , tolerance=p_tolerance
247 , credit_hold=p_credit_hold
248 , credit_rating=p_credit_rating
249 , last_update_date=p_last_update_date
250 , last_updated_by=p_last_updated_by
251 WHERE credit_profile_id=p_credit_profile_id;
252
253 IF (SQL%NOTFOUND)
254 THEN
255 RAISE NO_DATA_FOUND;
256 END IF;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
261 THEN
262 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Update_row');
263 END IF;
264 RAISE;
265
266 END Update_Row;
267
268 --========================================================================
269 -- PROCEDURE : Delete_row PUBLIC
270 -- PARAMETERS: p_credit_profile_id credit_profile_id
271 --
272 -- COMMENT : Procedure deletes record from the table HZ_CREDIT_PROFILES
273 --
274 --========================================================================
275 PROCEDURE Delete_row
276 ( p_credit_profile_id NUMBER
277 )
278 IS
279 BEGIN
280 DELETE
281 FROM HZ_CREDIT_PROFILES
282 WHERE credit_profile_id=p_credit_profile_id;
283
284 IF (SQL%NOTFOUND)
285 THEN
286 RAISE NO_DATA_FOUND;
287 END IF;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
292 THEN
293 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
294 END IF;
295 RAISE;
296
297 END Delete_row;
298
299 END HZ_CREDIT_PROFILES_PKG;