[Home] [Help]
PACKAGE BODY: APPS.ARP_CRM_PKG
Source
1 PACKAGE BODY arp_crm_pkg as
2 /* $Header: AROCRMB.pls 115.1 99/07/17 00:01:50 porting ship $ */
3 PROCEDURE check_unique ( x_cust_receipt_method_id in number,
4 x_receipt_method_id in number,
5 x_start_date in date,
6 x_end_date in date,
7 x_meth_type in varchar2,
8 x_id in number
9 ) is
10 overlap_count number;
11 begin
12 --
13 if (x_meth_type = 'CUST' ) then
14 --
15 SELECT count(1)
16 into overlap_count
17 FROM ra_cust_receipt_methods cpm
18 WHERE cpm.receipt_method_id = x_receipt_method_id
19 AND cpm.customer_id = x_id
20 AND cpm.site_use_id is null
21 AND ((x_cust_receipt_method_id is null) or (cpm.cust_receipt_method_id <> X_cust_receipt_method_id))
22 AND ( trunc(x_start_date) BETWEEN cpm.start_date
23 AND NVL(cpm.end_date, trunc(x_start_date))
24 or
25 cpm.start_date between x_start_date and nvl(x_end_date,cpm.start_date)
26 );
27 --
28 if (overlap_count >= 1) then
29 fnd_message.set_name('AR','AR_CUST_PAYMETH_OVERLAP');
30 app_exception.raise_exception;
31 end if;
32 elsif (x_meth_type = 'SITE') then
33 --
34 SELECT count(1)
35 into overlap_count
36 FROM ra_cust_receipt_methods cpm
37 WHERE cpm.receipt_method_id = x_receipt_method_id
38 AND cpm.site_use_id = x_id
39 AND ((x_cust_receipt_method_id is null) or (cpm.cust_receipt_method_id <> X_cust_receipt_method_id))
40 AND ( trunc(x_start_date) BETWEEN cpm.start_date
41 AND NVL(cpm.end_date, trunc(x_start_date))
42 or
43 cpm.start_date between x_start_date and nvl(x_end_date,cpm.start_date)
44 );
45 --
46 if (overlap_count >= 1) then
47 fnd_message.set_name('AR','AR_CUST_PAYMETH_OVERLAP');
48 app_exception.raise_exception;
49 end if;
50 else
51 app_exception.invalid_argument('arp_cust_receipt_methods_pkg.check_unique','meth_type',x_meth_type);
52 end if;
53 --
54 end check_unique;
55
56 PROCEDURE check_primary(x_cust_receipt_method_id in number,
57 x_start_date in date,
58 x_end_date in date,
59 x_meth_type in varchar2,
60 x_id in number
61 ) is
62 primary_count number;
63 begin
64 if (x_meth_type = 'CUST' ) then
65 SELECT count(1)
66 INTO primary_count
67 FROM ra_cust_receipt_methods cpm
68 WHERE cpm.primary_flag = 'Y'
69 AND cpm.customer_id = x_id
70 AND cpm.site_use_id is null
71 AND ((x_cust_receipt_method_id is null) or (cpm.cust_receipt_method_id <> x_cust_receipt_method_id))
72 AND ( trunc(x_start_date) BETWEEN cpm.start_date
73 AND NVL(cpm.end_date,TRUNC(x_start_date))
74 OR
75 cpm.start_date between x_start_date and nvl(x_end_date,cpm.start_date)
76 );
77
78
79 if (primary_count >= 1 ) then
80 fnd_message.set_name('AR','AR_CUST_PAYMETH_PRIM_OVERLAP');
81 app_exception.raise_exception;
82 end if;
83
84 elsif( x_meth_type = 'SITE' ) then
85 SELECT count(1)
86 INTO primary_count
87 FROM ra_cust_receipt_methods cpm
88 WHERE cpm.primary_flag = 'Y'
89 AND cpm.site_use_id = x_id
90 AND ((x_cust_receipt_method_id is null) or (cpm.cust_receipt_method_id <> x_cust_receipt_method_id))
91 AND (trunc(x_start_date) BETWEEN cpm.start_date
92 AND NVL(cpm.end_date,TRUNC(x_start_date))
93 OR
94 cpm.start_date between x_start_date and nvl(x_end_date,cpm.start_date)
95 );
96 if (primary_count >= 1 ) then
97 fnd_message.set_name('AR','AR_CUST_PAYMETH_PRIM_OVERLAP');
98 app_exception.raise_exception;
99 end if;
100
101 else
102 app_exception.invalid_argument('arp_cust_receipt_methods_pkg.check_primary','meth_type',x_meth_type);
103 end if;
104 --
105 end check_primary;
106
107 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
108 X_Cust_Receipt_Method_Id IN OUT NUMBER,
109 X_Created_By NUMBER,
110 X_Creation_Date DATE,
111 X_Customer_Id NUMBER,
112 X_Last_Updated_By NUMBER,
113 X_Last_Update_Date DATE,
114 X_Primary_Flag VARCHAR2,
115 X_Receipt_Method_Id NUMBER,
116 X_Start_Date DATE,
117 X_End_Date DATE,
118 X_Last_Update_Login NUMBER,
119 X_Site_Use_Id NUMBER,
120 X_Attribute_Category VARCHAR2,
121 X_Attribute1 VARCHAR2,
122 X_Attribute2 VARCHAR2,
123 X_Attribute3 VARCHAR2,
124 X_Attribute4 VARCHAR2,
125 X_Attribute5 VARCHAR2,
126 X_Attribute6 VARCHAR2,
127 X_Attribute7 VARCHAR2,
128 X_Attribute8 VARCHAR2,
129 X_Attribute9 VARCHAR2,
130 X_Attribute10 VARCHAR2,
131 X_Attribute11 VARCHAR2,
132 X_Attribute12 VARCHAR2,
133 X_Attribute13 VARCHAR2,
134 X_Attribute14 VARCHAR2,
135 X_Attribute15 VARCHAR2
136 ) IS
137 CURSOR C IS SELECT rowid FROM ra_cust_receipt_methods
138 WHERE cust_receipt_method_id = X_Cust_Receipt_Method_Id;
139
140 meth_type varchar2(4);
141 id number(15);
142 BEGIN
143 if ( x_site_use_id is null ) then
144 meth_type := 'CUST';
145 id := x_customer_id;
146 else
147 meth_type := 'SITE';
148 id := x_site_use_id;
149 end if;
150 --
151 check_unique(x_cust_receipt_method_id,x_receipt_method_id,x_start_date,x_end_date,meth_type,id);
152 --
153 if (x_primary_flag = 'Y') then
154 check_primary(x_cust_receipt_method_id,x_start_date,x_end_date,meth_type,id);
155 end if;
156
157 select ra_cust_receipt_methods_s.nextval
158 into x_cust_receipt_method_id
159 from dual;
160
161 INSERT INTO ra_cust_receipt_methods(
162 cust_receipt_method_id,
163 created_by,
164 creation_date,
165 customer_id,
166 last_updated_by,
167 last_update_date,
168 primary_flag,
169 receipt_method_id,
170 start_date,
171 end_date,
172 last_update_login,
173 site_use_id,
174 attribute_category,
175 attribute1,
176 attribute2,
177 attribute3,
178 attribute4,
179 attribute5,
180 attribute6,
181 attribute7,
182 attribute8,
183 attribute9,
184 attribute10,
185 attribute11,
186 attribute12,
187 attribute13,
188 attribute14,
189 attribute15
190 ) VALUES (
191
192 X_Cust_Receipt_Method_Id,
193 X_Created_By,
194 X_Creation_Date,
195 X_Customer_Id,
196 X_Last_Updated_By,
197 X_Last_Update_Date,
198 X_Primary_Flag,
199 X_Receipt_Method_Id,
200 X_Start_Date,
201 X_End_Date,
202 X_Last_Update_Login,
203 X_Site_Use_Id,
204 X_Attribute_Category,
205 X_Attribute1,
206 X_Attribute2,
207 X_Attribute3,
208 X_Attribute4,
209 X_Attribute5,
210 X_Attribute6,
211 X_Attribute7,
212 X_Attribute8,
213 X_Attribute9,
214 X_Attribute10,
215 X_Attribute11,
216 X_Attribute12,
217 X_Attribute13,
218 X_Attribute14,
219 X_Attribute15
220
221 );
222
223 OPEN C;
224 FETCH C INTO X_Rowid;
225 if (C%NOTFOUND) then
226 CLOSE C;
227 Raise NO_DATA_FOUND;
228 end if;
229 CLOSE C;
230 END Insert_Row;
231
232
233 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
234 X_Cust_Receipt_Method_Id NUMBER,
235 X_Customer_Id NUMBER,
236 X_Primary_Flag VARCHAR2,
237 X_Receipt_Method_Id NUMBER,
238 X_Start_Date DATE,
239 X_End_Date DATE,
240 X_Attribute_Category VARCHAR2,
241 X_Attribute1 VARCHAR2,
242 X_Attribute2 VARCHAR2,
243 X_Attribute3 VARCHAR2,
244 X_Attribute4 VARCHAR2,
245 X_Attribute5 VARCHAR2,
246 X_Attribute6 VARCHAR2,
247 X_Attribute7 VARCHAR2,
248 X_Attribute8 VARCHAR2,
249 X_Attribute9 VARCHAR2,
250 X_Attribute10 VARCHAR2,
251 X_Attribute11 VARCHAR2,
252 X_Attribute12 VARCHAR2,
253 X_Attribute13 VARCHAR2,
254 X_Attribute14 VARCHAR2,
255 X_Attribute15 VARCHAR2
256 ) IS
257 CURSOR C IS
258 SELECT *
259 FROM ra_cust_receipt_methods
260 WHERE rowid = X_Rowid
261 FOR UPDATE of Cust_Receipt_Method_Id NOWAIT;
262 Recinfo C%ROWTYPE;
263
264
265 BEGIN
266 OPEN C;
267 FETCH C INTO Recinfo;
268 if (C%NOTFOUND) then
269 CLOSE C;
270 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
271 APP_EXCEPTION.Raise_Exception;
272 end if;
273 CLOSE C;
274 if (
275
276 (Recinfo.cust_receipt_method_id = X_Cust_Receipt_Method_Id)
277 AND (Recinfo.customer_id = X_Customer_Id)
278 AND (Recinfo.primary_flag = X_Primary_Flag)
279 AND (Recinfo.receipt_method_id = X_Receipt_Method_Id)
280 AND (Recinfo.start_date = X_Start_Date)
281 AND ( (Recinfo.end_date = X_End_Date)
282 OR ( (Recinfo.end_date IS NULL)
283 AND (X_End_Date IS NULL)))
284 AND ( (Recinfo.attribute_category = X_Attribute_Category)
285 OR ( (Recinfo.attribute_category IS NULL)
286 AND (X_Attribute_Category IS NULL)))
287 AND ( (Recinfo.attribute1 = X_Attribute1)
288 OR ( (Recinfo.attribute1 IS NULL)
289 AND (X_Attribute1 IS NULL)))
290 AND ( (Recinfo.attribute2 = X_Attribute2)
291 OR ( (Recinfo.attribute2 IS NULL)
292 AND (X_Attribute2 IS NULL)))
293 AND ( (Recinfo.attribute3 = X_Attribute3)
294 OR ( (Recinfo.attribute3 IS NULL)
295 AND (X_Attribute3 IS NULL)))
296 AND ( (Recinfo.attribute4 = X_Attribute4)
297 OR ( (Recinfo.attribute4 IS NULL)
298 AND (X_Attribute4 IS NULL)))
299 AND ( (Recinfo.attribute5 = X_Attribute5)
300 OR ( (Recinfo.attribute5 IS NULL)
301 AND (X_Attribute5 IS NULL)))
302 AND ( (Recinfo.attribute6 = X_Attribute6)
303 OR ( (Recinfo.attribute6 IS NULL)
304 AND (X_Attribute6 IS NULL)))
305 AND ( (Recinfo.attribute7 = X_Attribute7)
306 OR ( (Recinfo.attribute7 IS NULL)
307 AND (X_Attribute7 IS NULL)))
308 AND ( (Recinfo.attribute8 = X_Attribute8)
309 OR ( (Recinfo.attribute8 IS NULL)
310 AND (X_Attribute8 IS NULL)))
311 AND ( (Recinfo.attribute9 = X_Attribute9)
312 OR ( (Recinfo.attribute9 IS NULL)
313 AND (X_Attribute9 IS NULL)))
314 AND ( (Recinfo.attribute10 = X_Attribute10)
315 OR ( (Recinfo.attribute10 IS NULL)
316 AND (X_Attribute10 IS NULL)))
317 AND ( (Recinfo.attribute11 = X_Attribute11)
318 OR ( (Recinfo.attribute11 IS NULL)
319 AND (X_Attribute11 IS NULL)))
320 AND ( (Recinfo.attribute12 = X_Attribute12)
321 OR ( (Recinfo.attribute12 IS NULL)
322 AND (X_Attribute12 IS NULL)))
323 AND ( (Recinfo.attribute13 = X_Attribute13)
324 OR ( (Recinfo.attribute13 IS NULL)
325 AND (X_Attribute13 IS NULL)))
326 AND ( (Recinfo.attribute14 = X_Attribute14)
327 OR ( (Recinfo.attribute14 IS NULL)
328 AND (X_Attribute14 IS NULL)))
329 AND ( (Recinfo.attribute15 = X_Attribute15)
330 OR ( (Recinfo.attribute15 IS NULL)
331 AND (X_Attribute15 IS NULL)))
332 ) then
333 return;
334 else
335 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
336 APP_EXCEPTION.Raise_Exception;
337 end if;
338 END Lock_Row;
339
340
341
342 PROCEDURE Update_Row(X_Rowid VARCHAR2,
343
344 X_Cust_Receipt_Method_Id NUMBER,
345 X_Customer_Id NUMBER,
346 X_Last_Updated_By NUMBER,
347 X_Last_Update_Date DATE,
348 X_Primary_Flag VARCHAR2,
349 X_Receipt_Method_Id NUMBER,
350 X_Start_Date DATE,
351 X_End_Date DATE,
352 X_Last_Update_Login NUMBER,
353 X_Site_Use_Id NUMBER,
354 X_Attribute_Category VARCHAR2,
355 X_Attribute1 VARCHAR2,
356 X_Attribute2 VARCHAR2,
357 X_Attribute3 VARCHAR2,
358 X_Attribute4 VARCHAR2,
359 X_Attribute5 VARCHAR2,
360 X_Attribute6 VARCHAR2,
361 X_Attribute7 VARCHAR2,
362 X_Attribute8 VARCHAR2,
363 X_Attribute9 VARCHAR2,
364 X_Attribute10 VARCHAR2,
365 X_Attribute11 VARCHAR2,
366 X_Attribute12 VARCHAR2,
367 X_Attribute13 VARCHAR2,
368 X_Attribute14 VARCHAR2,
369 X_Attribute15 VARCHAR2
370
371 ) IS
372 meth_type varchar2(4);
373 id number(15);
374 BEGIN
375 if ( x_site_use_id is null ) then
376 meth_type := 'CUST';
377 id := x_customer_id;
378 else
379 meth_type := 'SITE';
380 id := x_site_use_id;
381 end if;
382 --
383 check_unique(x_cust_receipt_method_id,x_receipt_method_id,x_start_date,x_end_date,meth_type,id);
384 --
385 if (x_primary_flag = 'Y') then
386 check_primary(x_cust_receipt_method_id,x_start_date,x_end_date,meth_type,id);
387 end if;
388
389 UPDATE ra_cust_receipt_methods
390 SET
391 cust_receipt_method_id = X_Cust_Receipt_Method_Id,
392 customer_id = X_Customer_Id,
393 last_updated_by = X_Last_Updated_By,
394 last_update_date = X_Last_Update_Date,
395 primary_flag = X_Primary_Flag,
396 receipt_method_id = X_Receipt_Method_Id,
397 start_date = X_Start_Date,
398 end_date = X_End_Date,
399 last_update_login = X_Last_Update_Login,
400 site_use_id = X_Site_Use_Id,
401 attribute_category = X_Attribute_Category,
402 attribute1 = X_Attribute1,
403 attribute2 = X_Attribute2,
404 attribute3 = X_Attribute3,
405 attribute4 = X_Attribute4,
406 attribute5 = X_Attribute5,
407 attribute6 = X_Attribute6,
408 attribute7 = X_Attribute7,
409 attribute8 = X_Attribute8,
410 attribute9 = X_Attribute9,
411 attribute10 = X_Attribute10,
412 attribute11 = X_Attribute11,
413 attribute12 = X_Attribute12,
414 attribute13 = X_Attribute13,
415 attribute14 = X_Attribute14,
416 attribute15 = X_Attribute15
417 WHERE rowid = X_Rowid;
418
419 if (SQL%NOTFOUND) then
420 Raise NO_DATA_FOUND;
421 end if;
422 END Update_Row;
423
424 END arp_crm_pkg;