[Home] [Help]
PACKAGE BODY: APPS.IEX_DUNNINGS_PKG
Source
1 PACKAGE BODY IEX_DUNNINGS_pkg AS
2 /* $Header: iextdunb.pls 120.7.12000000.2 2007/03/14 14:11:26 gnramasa ship $ */
3
4 PG_DEBUG NUMBER(2) ;
5
6 PROCEDURE insert_row(
7 px_rowid IN OUT NOCOPY VARCHAR2
8 , px_dunning_id IN OUT NOCOPY NUMBER
9 , p_template_id NUMBER
10 , p_callback_yn VARCHAR2
11 , p_callback_date DATE
12 , p_campaign_sched_id NUMBER
13 , p_status VARCHAR2
14 , p_delinquency_id NUMBER
15 , p_ffm_request_id NUMBER
16 , p_xml_request_id NUMBER
17 , p_xml_template_id NUMBER
18 , p_object_id NUMBER
19 , p_object_type VARCHAR2
20 , p_dunning_object_id NUMBER
21 , p_dunning_level VARCHAR2
22 , p_dunning_method VARCHAR2
23 , p_amount_due_remaining NUMBER
24 , p_currency_code VARCHAR2
25 , p_last_update_date DATE
26 , p_last_updated_by NUMBER
27 , p_creation_date DATE
28 , p_created_by NUMBER
29 , p_last_update_login NUMBER
30 , p_request_id NUMBER -- Added for bug 5661324 by gnramasa 14-Mar-07
31 , p_financial_charge NUMBER
32 , p_letter_name VARCHAR2
33 , p_interest_amt NUMBER
34 , p_dunning_plan_id NUMBER
35 , p_contact_destination varchar2
36 , p_contact_party_id NUMBER
37 , p_delivery_status varchar2 -- Added for bug 5661324 by gnramasa 14-Mar-07
38 , p_parent_dunning_id number -- Added for bug 5661324 by gnramasa 14-Mar-07
39 ) IS
40 CURSOR l_insert IS
41 SELECT ROWID
42 FROM iex_dunnings
43 WHERE dunning_id = px_dunning_id;
44 --
45 CURSOR get_seq_csr is
46 SELECT IEX_dunnings_s.nextval
47 FROM sys.dual;
48 BEGIN
49 --
50 If (px_dunning_id IS NULL) OR (px_dunning_id = FND_API.G_MISS_NUM) then
51 OPEN get_seq_csr;
52 FETCH get_seq_csr INTO px_dunning_id;
53 CLOSE get_seq_csr;
54 End If;
55 --
56 INSERT INTO IEX_DUNNINGS (
57 DUNNING_ID
58 , TEMPLATE_ID
59 , CALLBACK_YN
60 , CALLBACK_DATE
61 , CAMPAIGN_SCHED_ID
62 , STATUS
63 , DELINQUENCY_ID
64 , FFM_REQUEST_ID
65 , XML_REQUEST_ID
66 , XML_TEMPLATE_ID
67 , OBJECT_ID
68 , OBJECT_TYPE
69 , DUNNING_OBJECT_ID
70 , DUNNING_LEVEL
71 , DUNNING_METHOD
72 , AMOUNT_DUE_REMAINING
73 , CURRENCY_CODE
74 , last_update_date
75 , last_updated_by
76 , creation_date
77 , created_by
78 , last_update_login
79 , request_id
80 , financial_charge
81 , letter_name
82 , interest_amt
83 , dunning_plan_id
84 , contact_destination
85 , contact_party_id
86 , delivery_status
87 , parent_dunning_id
88 ) VALUES (
89 px_dunning_id
90 , DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
91 , DECODE(p_callback_YN, FND_API.G_MISS_CHAR, NULL, p_callback_YN)
92 , DECODE(p_callback_date, FND_API.G_MISS_DATE, NULL, p_callback_date)
93 , DECODE(p_campaign_sched_id, FND_API.G_MISS_NUM, NULL, p_campaign_sched_id)
94 , DECODE(p_status, FND_API.G_MISS_CHAR, NULL, p_status)
95 , DECODE(p_delinquency_id, FND_API.G_MISS_NUM, NULL, p_delinquency_id)
96 , DECODE(p_ffm_request_id, FND_API.G_MISS_NUM, NULL, p_ffm_request_id)
97 , DECODE(p_xml_request_id, FND_API.G_MISS_NUM, NULL, p_xml_request_id)
98 , DECODE(p_xml_template_id, FND_API.G_MISS_NUM, NULL, p_xml_template_id)
99 , DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id)
100 , DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type)
101 , DECODE(p_dunning_object_id, FND_API.G_MISS_NUM, NULL, p_dunning_object_id)
102 , DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
103 , DECODE(p_dunning_method, FND_API.G_MISS_CHAR, NULL, p_dunning_method)
104 , DECODE(p_amount_due_remaining, FND_API.G_MISS_NUM, NULL, p_amount_due_remaining)
105 , DECODE(p_currency_code, FND_API.G_MISS_CHAR, NULL, p_currency_code)
106 , DECODE(p_last_update_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_last_update_date)
107 , DECODE(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by)
108 , DECODE(p_creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_creation_date)
109 , DECODE(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by)
110 , DECODE(p_last_update_login, FND_API.G_MISS_NUM, NULL, p_last_update_login)
111 , DECODE(p_request_id, FND_API.G_MISS_NUM, NULL, p_request_id)
112 , DECODE(p_financial_charge, FND_API.G_MISS_NUM, NULL, p_financial_charge)
113 , DECODE(p_letter_name, FND_API.G_MISS_CHAR, NULL, p_letter_name)
114 , DECODE(p_interest_amt, FND_API.G_MISS_NUM, NULL, p_interest_amt)
115 , DECODE(p_dunning_plan_id, FND_API.G_MISS_NUM, NULL, p_dunning_plan_id)
116 , DECODE(p_contact_destination, FND_API.G_MISS_CHAR, NULL, p_contact_destination)
117 , DECODE(p_contact_party_id, FND_API.G_MISS_NUM, NULL, p_contact_party_id)
118 , DECODE(p_delivery_status, FND_API.G_MISS_CHAR, NULL, p_delivery_status)
119 , DECODE(p_parent_dunning_id, FND_API.G_MISS_NUM, NULL, p_parent_dunning_id)
120 );
121
122 OPEN l_insert;
123 FETCH l_insert INTO px_rowid;
124 IF (l_insert%NOTFOUND) THEN
125 CLOSE l_insert;
126 RAISE NO_DATA_FOUND;
127 END IF;
128 END insert_row;
129
130
131
132 PROCEDURE delete_row(
133 p_dunning_id NUMBER
134 ) IS
135 BEGIN
136 DELETE FROM iex_dunnings
137 WHERE dunning_id = p_dunning_id;
138 IF (SQL%NOTFOUND) THEN
139 RAISE NO_DATA_FOUND;
140 END IF;
141 END delete_row;
142
143
144 PROCEDURE update_row(
145 p_rowid VARCHAR2
146 , p_dunning_id NUMBER
147 , p_template_id NUMBER
148 , p_callback_yn VARCHAR2
149 , p_callback_date DATE
150 , p_campaign_sched_id NUMBER
151 , p_status VARCHAR2
152 , p_delinquency_id NUMBER
153 , p_ffm_request_id NUMBER
154 , p_xml_request_id NUMBER
155 , p_xml_template_id NUMBER
156 , p_object_id NUMBER
157 , p_object_type VARCHAR2
158 , p_dunning_object_id NUMBER
159 , p_dunning_level VARCHAR2
160 , p_dunning_method VARCHAR2
161 , p_amount_due_remaining NUMBER
162 , p_currency_code VARCHAR2
163 , p_last_update_date DATE
164 , p_last_updated_by NUMBER
165 , p_creation_date DATE
166 , p_created_by NUMBER
167 , p_last_update_login NUMBER
168 , p_request_id NUMBER
169 , p_financial_charge NUMBER
170 , p_letter_name VARCHAR2
171 , p_interest_amt NUMBER
172 , p_dunning_plan_id NUMBER
173 , p_contact_destination varchar2
174 , p_contact_party_id NUMBER
175 , p_delivery_status varchar2
176 , p_parent_dunning_id number
177 ) IS
178 BEGIN
179 UPDATE iex_dunnings
180 SET
181 dunning_id = DECODE(p_dunning_id, FND_API.G_MISS_NUM, NULL, p_dunning_id)
182 , template_id = DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
183 , callback_yn = DECODE(p_callback_yn, FND_API.G_MISS_CHAR, NULL, p_callback_yn)
184 , callback_date = DECODE(p_callback_date, FND_API.G_MISS_DATE, NULL, p_callback_date)
185 , campaign_sched_id = DECODE(p_campaign_sched_id, FND_API.G_MISS_NUM, NULL, p_campaign_sched_id)
186 , status = DECODE(p_status, FND_API.G_MISS_CHAR, NULL, p_status)
187 , delinquency_id = DECODE(p_delinquency_id, FND_API.G_MISS_NUM, NULL, p_delinquency_id)
188 , ffm_request_id = DECODE(p_ffm_request_id, FND_API.G_MISS_NUM, NULL, p_ffm_request_id)
189 , xml_request_id = DECODE(p_xml_request_id, FND_API.G_MISS_NUM, NULL, p_xml_request_id)
190 , xml_template_id = DECODE(p_xml_template_id, FND_API.G_MISS_NUM, NULL, p_xml_template_id)
191 , object_id = DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id)
192 , object_type = DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type)
193 , dunning_object_id = DECODE(p_dunning_object_id, FND_API.G_MISS_NUM, NULL, p_dunning_object_id)
194 , dunning_level = DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
195 , dunning_method = DECODE(p_dunning_method, FND_API.G_MISS_CHAR, NULL, p_dunning_method)
196 , amount_due_remaining = DECODE(p_amount_due_remaining, FND_API.G_MISS_NUM, NULL, p_amount_due_remaining)
197 , currency_code = DECODE(p_currency_code, FND_API.G_MISS_CHAR, NULL, p_currency_code)
198 , last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_last_update_date)
199 , last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,NULL,p_last_updated_by)
200 , creation_date = DECODE(p_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_creation_date)
201 , created_by = DECODE(p_created_by,FND_API.G_MISS_NUM,NULL,p_created_by)
202 , last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,NULL,p_last_update_login)
203 , request_id = DECODE(p_request_id,FND_API.G_MISS_NUM,NULL,p_request_id)
204 , financial_charge = DECODE(p_financial_charge, FND_API.G_MISS_NUM, NULL, p_financial_charge)
205 , letter_name = DECODE(p_letter_name, FND_API.G_MISS_CHAR, NULL, p_letter_name)
206 , interest_amt = DECODE(p_interest_amt, FND_API.G_MISS_NUM, NULL, p_interest_amt)
207 , dunning_plan_id = DECODE(p_dunning_plan_id, FND_API.G_MISS_NUM, NULL, p_dunning_plan_id)
208 , contact_destination = DECODE(p_contact_destination, FND_API.G_MISS_CHAR, NULL, p_contact_destination)
209 , contact_party_id = DECODE(p_contact_party_id, FND_API.G_MISS_NUM, NULL, p_contact_party_id)
210 , delivery_status = DECODE(p_delivery_status, FND_API.G_MISS_CHAR, NULL, p_delivery_status)
211 , parent_dunning_id = DECODE(p_parent_dunning_id, FND_API.G_MISS_NUM, NULL, p_parent_dunning_id)
212 WHERE ROWID = p_rowid;
213 IF (SQL%NOTFOUND) THEN
214 RAISE NO_DATA_FOUND;
215 END IF;
216 END update_row;
217
218
219
220 PROCEDURE lock_row(
221 p_rowid VARCHAR2
222 , p_dunning_id NUMBER
223 , p_template_id NUMBER
224 , p_callback_yn VARCHAR2
225 , p_callback_date DATE
226 , p_campaign_sched_id NUMBER
227 , p_status VARCHAR2
228 , p_delinquency_id NUMBER
229 , p_ffm_request_id NUMBER
230 , p_xml_request_id NUMBER
231 , p_xml_template_id NUMBER
232 , p_object_id NUMBER
233 , p_object_type VARCHAR2
234 , p_dunning_object_id NUMBER
235 , p_dunning_level VARCHAR2
236 , p_dunning_method VARCHAR2
237 , p_amount_due_remaining NUMBER
238 , p_currency_code VARCHAR2
239 , p_last_update_date DATE
240 , p_last_updated_by NUMBER
241 , p_creation_date DATE
242 , p_created_by NUMBER
243 , p_last_update_login NUMBER
244 , p_financial_charge NUMBER
245 , p_letter_name VARCHAR2
246 , p_interest_amt NUMBER
247 , p_dunning_plan_id NUMBER
248 , p_contact_destination varchar2
249 , p_contact_party_id NUMBER
250 ) IS
251 CURSOR l_lock IS
252 SELECT *
253 FROM iex_dunnings
254 WHERE rowid = p_rowid
255 FOR UPDATE OF dunning_id NOWAIT;
256 l_table_rec l_lock%ROWTYPE;
257 BEGIN
258 OPEN l_lock;
259 FETCH l_lock INTO l_table_rec;
260 IF (l_lock%NOTFOUND) THEN
261 CLOSE l_lock;
262 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
263 APP_EXCEPTION.RAISE_EXCEPTION;
264 END IF;
265 CLOSE l_lock;
266 IF (
267 ((l_table_rec.dunning_id = p_dunning_id)
268 OR ((l_table_rec.dunning_id IS NULL)
269 AND ( p_dunning_id IS NULL)))
270 AND ((l_table_rec.template_id = p_template_id)
271 OR ((l_table_rec.template_id IS NULL)
272 AND ( p_template_id IS NULL)))
273 AND ((l_table_rec.callback_yn = p_callback_yn)
274 OR ((l_table_rec.callback_yn IS NULL)
275 AND ( p_callback_yn IS NULL)))
276 AND ((l_table_rec.callback_date = p_callback_date)
277 OR ((l_table_rec.callback_date IS NULL)
278 AND ( p_callback_date IS NULL)))
279 AND ((l_table_rec.campaign_sched_id = p_campaign_sched_id)
280 OR ((l_table_rec.campaign_sched_id IS NULL)
281 AND ( p_campaign_sched_id IS NULL)))
282 AND ((l_table_rec.status = p_status)
283 OR ((l_table_rec.status IS NULL)
284 AND ( p_status IS NULL)))
285 AND ((l_table_rec.delinquency_id = p_delinquency_id)
286 OR ((l_table_rec.delinquency_id IS NULL)
287 AND ( p_delinquency_id IS NULL)))
288 AND ((l_table_rec.ffm_request_id = p_ffm_request_id)
289 OR ((l_table_rec.ffm_request_id IS NULL)
290 AND ( p_ffm_request_id IS NULL)))
291 AND ((l_table_rec.xml_request_id = p_xml_request_id)
292 OR ((l_table_rec.xml_request_id IS NULL)
293 AND ( p_xml_request_id IS NULL)))
294 AND ((l_table_rec.xml_template_id = p_xml_template_id)
295 OR ((l_table_rec.xml_template_id IS NULL)
296 AND ( p_xml_template_id IS NULL)))
297 AND ((l_table_rec.object_id = p_object_id)
298 OR ((l_table_rec.object_id IS NULL)
299 AND ( p_object_id IS NULL)))
300 AND ((l_table_rec.object_type = p_object_type)
301 OR ((l_table_rec.object_type IS NULL)
302 AND ( p_object_type IS NULL)))
303 AND ((l_table_rec.dunning_object_id = p_dunning_object_id)
304 OR ((l_table_rec.dunning_object_id IS NULL)
305 AND ( p_dunning_object_id IS NULL)))
306 AND ((l_table_rec.dunning_level = p_dunning_level)
307 OR ((l_table_rec.dunning_level IS NULL)
308 AND ( p_dunning_level IS NULL)))
309 AND ((l_table_rec.dunning_method = p_dunning_method)
310 OR ((l_table_rec.dunning_method IS NULL)
311 AND ( p_dunning_method IS NULL)))
312 AND ((l_table_rec.amount_due_remaining = p_amount_due_remaining)
313 OR ((l_table_rec.amount_due_remaining IS NULL)
314 AND ( p_amount_due_remaining IS NULL)))
315 AND ((l_table_rec.currency_code = p_currency_code)
316 OR ((l_table_rec.currency_code IS NULL)
317 AND ( p_currency_code IS NULL)))
318 AND ((l_table_rec.last_update_date = p_last_update_date)
319 OR ((l_table_rec.last_update_date IS NULL)
320 AND ( p_last_update_date IS NULL)))
321 AND ((l_table_rec.last_updated_by = p_last_updated_by)
322 OR ((l_table_rec.last_updated_by IS NULL)
323 AND ( p_last_updated_by IS NULL)))
324 AND ((l_table_rec.creation_date = p_creation_date)
325 OR ((l_table_rec.creation_date IS NULL)
326 AND ( p_creation_date IS NULL)))
327 AND ((l_table_rec.created_by = p_created_by)
328 OR ((l_table_rec.created_by IS NULL)
329 AND ( p_created_by IS NULL)))
330 AND ((l_table_rec.last_update_login = p_last_update_login)
331 OR ((l_table_rec.last_update_login IS NULL)
332 AND ( p_last_update_login IS NULL)))
333 AND ((l_table_rec.financial_charge = p_financial_charge)
334 OR ((l_table_rec.financial_charge IS NULL)
335 AND ( p_financial_charge IS NULL)))
336 AND ((l_table_rec.letter_name = p_letter_name)
337 OR ((l_table_rec.letter_name IS NULL)
338 AND ( p_letter_name IS NULL)))
339 AND ((l_table_rec.interest_amt = p_interest_amt)
340 OR ((l_table_rec.interest_amt IS NULL)
341 AND ( p_interest_amt IS NULL)))
342 AND ((l_table_rec.dunning_plan_id = p_dunning_plan_id)
343 OR ((l_table_rec.dunning_plan_id IS NULL)
344 AND ( p_dunning_plan_id IS NULL)))
345 AND ((l_table_rec.contact_destination = p_contact_destination)
346 OR ((l_table_rec.contact_destination IS NULL)
347 AND ( p_contact_destination IS NULL)))
348 AND ((l_table_rec.contact_party_id = p_contact_party_id)
349 OR ((l_table_rec.contact_party_id IS NULL)
350 AND ( p_contact_party_id IS NULL)))
351 ) THEN
352 RETURN;
353 ELSE
354 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
355 APP_EXCEPTION.RAISE_EXCEPTION;
356 END IF;
357 END lock_row;
358
359 BEGIN
360 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
361
362 END iex_dunnings_pkg;