1 PACKAGE BODY HZ_PAYMENT_METHOD_PKG AS
2 /*$Header: ARHPYMTB.pls 120.0 2005/07/06 21:12:55 acng noship $ */
3
4 PROCEDURE Insert_Row(
5 x_cust_receipt_method_id IN OUT NOCOPY NUMBER,
6 x_customer_id IN NUMBER,
7 x_receipt_method_id IN NUMBER,
8 x_primary_flag IN VARCHAR2,
9 x_site_use_id IN NUMBER,
10 x_start_date IN DATE,
11 x_end_date IN DATE,
12 x_attribute_category IN VARCHAR2,
13 x_attribute1 IN VARCHAR2,
14 x_attribute2 IN VARCHAR2,
15 x_attribute3 IN VARCHAR2,
16 x_attribute4 IN VARCHAR2,
17 x_attribute5 IN VARCHAR2,
18 x_attribute6 IN VARCHAR2,
19 x_attribute7 IN VARCHAR2,
20 x_attribute8 IN VARCHAR2,
21 x_attribute9 IN VARCHAR2,
22 x_attribute10 IN VARCHAR2,
23 x_attribute11 IN VARCHAR2,
24 x_attribute12 IN VARCHAR2,
25 x_attribute13 IN VARCHAR2,
26 x_attribute14 IN VARCHAR2,
27 x_attribute15 IN VARCHAR2
28 ) IS
29 l_success VARCHAR2(1) := 'N';
30 l_primary_key_passed BOOLEAN := FALSE;
31 BEGIN
32 IF x_cust_receipt_method_id IS NOT NULL AND
33 x_cust_receipt_method_id <> fnd_api.g_miss_num
34 THEN
35 l_primary_key_passed := TRUE;
36 END IF;
37
38 WHILE l_success = 'N' LOOP
39 BEGIN
40 INSERT INTO RA_CUST_RECEIPT_METHODS(
41 cust_receipt_method_id,
42 customer_id,
43 receipt_method_id,
44 primary_flag,
45 site_use_id,
46 start_date,
47 end_date,
48 attribute_category,
49 attribute1,
50 attribute2,
51 attribute3,
52 attribute4,
53 attribute5,
54 attribute6,
55 attribute7,
56 attribute8,
57 attribute9,
58 attribute10,
59 attribute11,
60 attribute12,
61 attribute13,
62 attribute14,
63 attribute15,
64 last_update_date,
65 last_updated_by,
66 creation_date,
67 created_by,
68 last_update_login,
69 request_id,
70 program_application_id,
71 program_id,
72 program_update_date
73 ) VALUES (
74 DECODE(x_cust_receipt_method_id, fnd_api.g_miss_num,
75 ra_cust_receipt_methods_s.NEXTVAL,
76 NULL, ra_cust_receipt_methods_s.NEXTVAL, x_cust_receipt_method_id),
77 DECODE(x_customer_id, fnd_api.g_miss_num, NULL, x_customer_id),
78 DECODE(x_receipt_method_id, fnd_api.g_miss_num, NULL, x_receipt_method_id),
79 DECODE(x_primary_flag, fnd_api.g_miss_char, NULL, x_primary_flag),
80 DECODE(x_site_use_id, fnd_api.g_miss_num, NULL, x_site_use_id),
81 DECODE(x_start_date, fnd_api.g_miss_date, NULL, x_start_date),
82 DECODE(x_end_date, fnd_api.g_miss_date, NULL, x_end_date),
83 DECODE(x_attribute_category, fnd_api.g_miss_char, NULL, x_attribute_category),
84 DECODE(x_attribute1, fnd_api.g_miss_char, NULL, x_attribute1),
85 DECODE(x_attribute2, fnd_api.g_miss_char, NULL, x_attribute2),
86 DECODE(x_attribute3, fnd_api.g_miss_char, NULL, x_attribute3),
87 DECODE(x_attribute4, fnd_api.g_miss_char, NULL, x_attribute4),
88 DECODE(x_attribute5, fnd_api.g_miss_char, NULL, x_attribute5),
89 DECODE(x_attribute6, fnd_api.g_miss_char, NULL, x_attribute6),
90 DECODE(x_attribute7, fnd_api.g_miss_char, NULL, x_attribute7),
91 DECODE(x_attribute8, fnd_api.g_miss_char, NULL, x_attribute8),
92 DECODE(x_attribute9, fnd_api.g_miss_char, NULL, x_attribute9),
93 DECODE(x_attribute10, fnd_api.g_miss_char, NULL, x_attribute10),
94 DECODE(x_attribute11, fnd_api.g_miss_char, NULL, x_attribute11),
95 DECODE(x_attribute12, fnd_api.g_miss_char, NULL, x_attribute12),
96 DECODE(x_attribute13, fnd_api.g_miss_char, NULL, x_attribute13),
97 DECODE(x_attribute14, fnd_api.g_miss_char, NULL, x_attribute14),
98 DECODE(x_attribute15, fnd_api.g_miss_char, NULL, x_attribute15),
99 hz_utility_v2pub.last_update_date,
100 hz_utility_v2pub.last_updated_by,
101 hz_utility_v2pub.creation_date,
102 hz_utility_v2pub.created_by,
103 hz_utility_v2pub.last_update_login,
104 hz_utility_v2pub.request_id,
105 hz_utility_v2pub.program_application_id,
106 hz_utility_v2pub.program_id,
107 hz_utility_v2pub.program_update_date
108 ) RETURNING
109 cust_receipt_method_id
110 INTO
111 x_cust_receipt_method_id;
112
113 l_success := 'Y';
114
115 EXCEPTION
116 WHEN DUP_VAL_ON_INDEX THEN
117 IF INSTRB(SQLERRM, 'RA_CUST_RECEIPT_METHODS_U1') <> 0 OR
118 INSTRB(SQLERRM, 'RA_CUST_RECEIPT_METHODS_PK') <> 0
119 THEN
120 IF l_primary_key_passed THEN
121 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
122 fnd_message.set_token('COLUMN', 'cust_receipt_method_id');
123 fnd_msg_pub.add;
124 RAISE fnd_api.g_exc_error;
125 END IF;
126
127 DECLARE
128 l_temp_crm_id NUMBER;
129 l_max_crm_id NUMBER;
130 BEGIN
131 l_temp_crm_id := 0;
132 SELECT max(cust_receipt_method_id) INTO l_max_crm_id
133 FROM RA_CUST_RECEIPT_METHODS;
134 WHILE l_temp_crm_id <= l_max_crm_id LOOP
135 SELECT RA_CUST_RECEIPT_METHODS_S.NEXTVAL
136 INTO l_temp_crm_id FROM dual;
137 END LOOP;
138 END;
139 ELSE
140 RAISE;
141 END IF;
142 END;
143 END LOOP;
144 End Insert_Row;
145
146 PROCEDURE Update_Row(
147 x_rowid IN OUT NOCOPY VARCHAR2,
148 x_cust_receipt_method_id IN NUMBER,
149 x_customer_id IN NUMBER,
150 x_receipt_method_id IN NUMBER,
151 x_primary_flag IN VARCHAR2,
152 x_site_use_id IN NUMBER,
153 x_start_date IN DATE,
154 x_end_date IN DATE,
155 x_attribute_category IN VARCHAR2,
156 x_attribute1 IN VARCHAR2,
157 x_attribute2 IN VARCHAR2,
158 x_attribute3 IN VARCHAR2,
159 x_attribute4 IN VARCHAR2,
160 x_attribute5 IN VARCHAR2,
161 x_attribute6 IN VARCHAR2,
162 x_attribute7 IN VARCHAR2,
163 x_attribute8 IN VARCHAR2,
164 x_attribute9 IN VARCHAR2,
165 x_attribute10 IN VARCHAR2,
166 x_attribute11 IN VARCHAR2,
167 x_attribute12 IN VARCHAR2,
168 x_attribute13 IN VARCHAR2,
169 x_attribute14 IN VARCHAR2,
170 x_attribute15 IN VARCHAR2
171 ) IS
172
173 BEGIN
174
175 UPDATE ra_cust_receipt_methods
176 SET cust_receipt_method_id = DECODE(x_cust_receipt_method_id, NULL,
177 cust_receipt_method_id, fnd_api.g_miss_num, NULL,
178 x_cust_receipt_method_id),
179 customer_id = DECODE(x_customer_id, NULL, customer_id, fnd_api.g_miss_num,
180 NULL, x_customer_id),
181 receipt_method_id = DECODE(x_receipt_method_id, NULL, receipt_method_id,
182 fnd_api.g_miss_num, NULL, x_receipt_method_id),
183 primary_flag = DECODE(x_primary_flag, NULL, primary_flag,
184 fnd_api.g_miss_char, NULL, x_primary_flag),
185 site_use_id = DECODE(x_site_use_id, NULL, site_use_id,
186 fnd_api.g_miss_num, NULL, x_site_use_id),
187 start_date = DECODE(x_start_date, NULL, start_date,
188 fnd_api.g_miss_date, NULL, x_start_date),
189 end_date = DECODE(x_end_date, NULL, end_date,
190 fnd_api.g_miss_date, NULL, x_end_date),
191 attribute_category = DECODE(x_attribute_category, NULL, attribute_category,
192 fnd_api.g_miss_char, NULL, x_attribute_category),
193 attribute1 = DECODE(x_attribute1, NULL, attribute1, fnd_api.g_miss_char,
194 NULL, x_attribute1),
195 attribute2 = DECODE(x_attribute2, NULL, attribute2, fnd_api.g_miss_char,
196 NULL, x_attribute2),
197 attribute3 = DECODE(x_attribute3, NULL, attribute3, fnd_api.g_miss_char,
198 NULL, x_attribute3),
199 attribute4 = DECODE(x_attribute4, NULL, attribute4, fnd_api.g_miss_char,
200 NULL, x_attribute4),
201 attribute5 = DECODE(x_attribute5, NULL, attribute5, fnd_api.g_miss_char,
202 NULL, x_attribute5),
203 attribute6 = DECODE(x_attribute6, NULL, attribute6, fnd_api.g_miss_char,
204 NULL, x_attribute6),
205 attribute7 = DECODE(x_attribute7, NULL, attribute7, fnd_api.g_miss_char,
206 NULL, x_attribute7),
207 attribute8 = DECODE(x_attribute8, NULL, attribute8, fnd_api.g_miss_char,
208 NULL, x_attribute8),
209 attribute9 = DECODE(x_attribute9, NULL, attribute9, fnd_api.g_miss_char,
210 NULL, x_attribute9),
211 attribute10 = DECODE(x_attribute10, NULL, attribute10, fnd_api.g_miss_char,
212 NULL, x_attribute10),
213 last_update_date = hz_utility_v2pub.last_update_date,
214 last_updated_by = hz_utility_v2pub.last_updated_by,
215 creation_date = creation_date,
216 created_by = created_by,
217 last_update_login = hz_utility_v2pub.last_update_login,
218 request_id = hz_utility_v2pub.request_id,
219 program_application_id = hz_utility_v2pub.program_application_id,
220 program_id = hz_utility_v2pub.program_id,
221 program_update_date = hz_utility_v2pub.program_update_date
222 WHERE rowid = x_rowid;
223
224 If (SQL%NOTFOUND) then
225 RAISE NO_DATA_FOUND;
226 End If;
227 END Update_Row;
228
229 PROCEDURE Lock_Row(
230 x_rowid IN OUT NOCOPY VARCHAR2,
231 x_cust_receipt_method_id IN NUMBER,
232 x_customer_id IN NUMBER,
233 x_receipt_method_id IN NUMBER,
234 x_primary_flag IN VARCHAR2,
235 x_site_use_id IN NUMBER,
236 x_start_date IN DATE,
237 x_end_date IN DATE,
238 x_attribute_category IN VARCHAR2,
239 x_attribute1 IN VARCHAR2,
240 x_attribute2 IN VARCHAR2,
241 x_attribute3 IN VARCHAR2,
242 x_attribute4 IN VARCHAR2,
243 x_attribute5 IN VARCHAR2,
244 x_attribute6 IN VARCHAR2,
245 x_attribute7 IN VARCHAR2,
246 x_attribute8 IN VARCHAR2,
247 x_attribute9 IN VARCHAR2,
248 x_attribute10 IN VARCHAR2,
249 x_attribute11 IN VARCHAR2,
250 x_attribute12 IN VARCHAR2,
251 x_attribute13 IN VARCHAR2,
252 x_attribute14 IN VARCHAR2,
253 x_attribute15 IN VARCHAR2,
254 x_last_update_date IN DATE,
255 x_last_updated_by IN NUMBER,
256 x_creation_date IN DATE,
257 x_created_by IN NUMBER,
258 x_last_update_login IN NUMBER,
259 x_request_id IN NUMBER,
260 x_program_application_id IN NUMBER,
261 x_program_id IN NUMBER,
262 x_program_update_date IN DATE
263 ) IS
264
265 CURSOR c IS
266 SELECT *
267 FROM ra_cust_receipt_methods
268 WHERE ROWID = x_rowid
269 FOR UPDATE NOWAIT;
270
271 recinfo c%ROWTYPE;
272 BEGIN
273 OPEN c;
274 FETCH c INTO recinfo;
275 IF (C%NOTFOUND) THEN
276 CLOSE c;
277 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
278 app_exception.raise_exception;
279 END IF;
280 CLOSE c;
281
282 IF (((recinfo.cust_receipt_method_id = x_cust_receipt_method_id)
283 OR ((recinfo.cust_receipt_method_id IS NULL)
284 AND (x_cust_receipt_method_id IS NULL)))
285 AND ((recinfo.customer_id = x_customer_id)
286 OR ((recinfo.customer_id IS NULL)
287 AND (x_customer_id IS NULL)))
288 AND ((recinfo.receipt_method_id = x_receipt_method_id)
289 OR ((recinfo.receipt_method_id IS NULL)
290 AND (x_receipt_method_id IS NULL)))
291 AND ((recinfo.primary_flag = x_primary_flag)
292 OR ((recinfo.primary_flag IS NULL)
293 AND (x_primary_flag IS NULL)))
294 AND ((recinfo.site_use_id = x_site_use_id)
295 OR ((recinfo.site_use_id IS NULL)
296 AND (x_site_use_id IS NULL)))
297 AND ((recinfo.start_date = x_start_date)
298 OR ((recinfo.start_date IS NULL)
299 AND (x_start_date IS NULL)))
300 AND ((recinfo.end_date = x_end_date)
301 OR ((recinfo.end_date IS NULL)
302 AND (x_start_date IS NULL)))
303 AND ((recinfo.attribute_category = x_attribute_category)
304 OR ((recinfo.attribute_category IS NULL)
305 AND (x_attribute_category IS NULL)))
306 AND ((recinfo.attribute1 = x_attribute1)
307 OR ((recinfo.attribute1 IS NULL)
308 AND (x_attribute1 IS NULL)))
309 AND ((recinfo.attribute2 = x_attribute2)
310 OR ((recinfo.attribute2 IS NULL)
311 AND (x_attribute2 IS NULL)))
312 AND ((recinfo.attribute3 = x_attribute3)
313 OR ((recinfo.attribute3 IS NULL)
314 AND (x_attribute3 IS NULL)))
315 AND ((recinfo.attribute4 = x_attribute4)
316 OR ((recinfo.attribute4 IS NULL)
317 AND (x_attribute4 IS NULL)))
318 AND ((recinfo.attribute5 = x_attribute5)
319 OR ((recinfo.attribute5 IS NULL)
320 AND (x_attribute5 IS NULL)))
321 AND ((recinfo.attribute6 = x_attribute6)
322 OR ((recinfo.attribute6 IS NULL)
323 AND (x_attribute6 IS NULL)))
324 AND ((recinfo.attribute7 = x_attribute7)
325 OR ((recinfo.attribute7 IS NULL)
326 AND (x_attribute7 IS NULL)))
327 AND ((recinfo.attribute8 = x_attribute8)
328 OR ((recinfo.attribute8 IS NULL)
329 AND (x_attribute8 IS NULL)))
330 AND ((recinfo.attribute9 = x_attribute9)
331 OR ((recinfo.attribute9 IS NULL)
332 AND (x_attribute9 IS NULL)))
333 AND ((recinfo.attribute10 = x_attribute10)
334 OR ((recinfo.attribute10 IS NULL)
335 AND (x_attribute10 IS NULL)))
336 AND ((recinfo.attribute11 = x_attribute11)
337 OR ((recinfo.attribute11 IS NULL)
338 AND (x_attribute11 IS NULL)))
339 AND ((recinfo.attribute12 = x_attribute12)
340 OR ((recinfo.attribute12 IS NULL)
341 AND (x_attribute12 IS NULL)))
342 AND ((recinfo.attribute13 = x_attribute13)
343 OR ((recinfo.attribute13 IS NULL)
344 AND (x_attribute13 IS NULL)))
345 AND ((recinfo.attribute14 = x_attribute14)
346 OR ((recinfo.attribute14 IS NULL)
347 AND (x_attribute14 IS NULL)))
348 AND ((recinfo.attribute15 = x_attribute15)
349 OR ((recinfo.attribute15 IS NULL)
350 AND (x_attribute15 IS NULL)))
351 AND ((recinfo.last_update_date = x_last_update_date)
352 OR ((recinfo.last_update_date IS NULL)
353 AND (x_last_update_date IS NULL)))
354 AND ((recinfo.last_updated_by = x_last_updated_by)
355 OR ((recinfo.last_updated_by IS NULL)
356 AND (x_last_updated_by IS NULL)))
357 AND ((recinfo.creation_date = x_creation_date)
358 OR ((recinfo.creation_date IS NULL)
359 AND (x_creation_date IS NULL)))
360 AND ((recinfo.created_by = x_created_by)
361 OR ((recinfo.created_by IS NULL)
362 AND (x_created_by IS NULL)))
363 AND ((recinfo.last_update_login = x_last_update_login)
364 OR ((recinfo.last_update_login IS NULL)
365 AND (x_last_update_login IS NULL)))
366 AND ((recinfo.request_id = x_request_id)
367 OR ((recinfo.request_id IS NULL)
368 AND (x_request_id IS NULL)))
369 AND ((recinfo.program_application_id = x_program_application_id)
370 OR ((recinfo.program_application_id IS NULL)
371 AND (x_program_application_id IS NULL)))
372 AND ((recinfo.program_id = x_program_id)
373 OR ((recinfo.program_id IS NULL)
374 AND (x_program_id IS NULL)))
375 AND ((recinfo.program_update_date = x_program_update_date)
376 OR ((recinfo.program_update_date IS NULL)
377 AND (x_program_update_date IS NULL)))
378 )
379 THEN
380 RETURN;
381 ELSE
382 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
383 app_exception.raise_exception;
384 END IF;
385 END lock_row;
386
387
388 PROCEDURE delete_row (x_cust_receipt_method_id IN NUMBER) IS
389 BEGIN
390 DELETE FROM ra_cust_receipt_methods
391 WHERE cust_receipt_method_id = x_cust_receipt_method_id;
392
393 IF (SQL%NOTFOUND) THEN
394 RAISE NO_DATA_FOUND;
395 END IF;
396 END delete_row;
397
398 END HZ_PAYMENT_METHOD_PKG;
[Home] [Help]