[Home] [Help]
PACKAGE BODY: APPS.IEX_DUNNINGS_PKG
Source
1 PACKAGE BODY IEX_DUNNINGS_pkg AS
2 /* $Header: iextdunb.pls 120.14 2011/06/14 06:36:19 snuthala 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 , p_dunning_mode varchar2 -- added by gnramasa for bug 8489610 14-May-09
40 , p_confirmation_mode varchar2 -- added by gnramasa for bug 8489610 14-May-09
41 , p_org_id number -- added for bug 9151851 PNAVEENK
42 , p_ag_dn_xref_id number
43 , p_correspondence_date date
44 , p_addt_xml_template_id number --added for bug 9970624 gnramasa 4th Aug 10
45 , p_addt_delivery_status varchar2 --added for bug 9970624 gnramasa 4th Aug 10
46 , p_as_of_date date
47 ) IS
48 CURSOR l_insert IS
49 SELECT ROWID
50 FROM iex_dunnings
51 WHERE dunning_id = px_dunning_id;
52 --
53 CURSOR get_seq_csr is
54 SELECT IEX_dunnings_s.nextval
55 FROM sys.dual;
56 BEGIN
57 --
58 If (px_dunning_id IS NULL) OR (px_dunning_id = FND_API.G_MISS_NUM) then
59 OPEN get_seq_csr;
60 FETCH get_seq_csr INTO px_dunning_id;
61 CLOSE get_seq_csr;
62 End If;
63 --Start adding for bug 8489610 by gnramasa 14-May-09
64 --
65 INSERT INTO IEX_DUNNINGS (
66 DUNNING_ID
67 , TEMPLATE_ID
68 , CALLBACK_YN
69 , CALLBACK_DATE
70 , CAMPAIGN_SCHED_ID
71 , STATUS
72 , DELINQUENCY_ID
73 , FFM_REQUEST_ID
74 , XML_REQUEST_ID
75 , XML_TEMPLATE_ID
76 , OBJECT_ID
77 , OBJECT_TYPE
78 , DUNNING_OBJECT_ID
79 , DUNNING_LEVEL
80 , DUNNING_METHOD
81 , AMOUNT_DUE_REMAINING
82 , CURRENCY_CODE
83 , last_update_date
84 , last_updated_by
85 , creation_date
86 , created_by
87 , last_update_login
88 , request_id
89 , financial_charge
90 , letter_name
91 , interest_amt
92 , dunning_plan_id
93 , contact_destination
94 , contact_party_id
95 , delivery_status
96 , parent_dunning_id
97 , dunning_mode
98 , confirmation_mode
99 , org_id -- added for bug 9151851
100 , ag_dn_xref_id
101 , correspondence_date
102 , addt_xml_template_id
103 , addt_delivery_status
104 , as_of_date
105 ) VALUES (
106 px_dunning_id
107 , DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
108 , DECODE(p_callback_YN, FND_API.G_MISS_CHAR, NULL, p_callback_YN)
109 , DECODE(p_callback_date, FND_API.G_MISS_DATE, NULL, p_callback_date)
110 , DECODE(p_campaign_sched_id, FND_API.G_MISS_NUM, NULL, p_campaign_sched_id)
111 , DECODE(p_status, FND_API.G_MISS_CHAR, NULL, p_status)
112 , DECODE(p_delinquency_id, FND_API.G_MISS_NUM, NULL, p_delinquency_id)
113 , DECODE(p_ffm_request_id, FND_API.G_MISS_NUM, NULL, p_ffm_request_id)
114 , DECODE(p_xml_request_id, FND_API.G_MISS_NUM, NULL, p_xml_request_id)
115 , DECODE(p_xml_template_id, FND_API.G_MISS_NUM, NULL, p_xml_template_id)
116 , DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id)
117 , DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type)
118 , DECODE(p_dunning_object_id, FND_API.G_MISS_NUM, NULL, p_dunning_object_id)
119 , DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
120 , DECODE(p_dunning_method, FND_API.G_MISS_CHAR, NULL, p_dunning_method)
121 , DECODE(p_amount_due_remaining, FND_API.G_MISS_NUM, NULL, p_amount_due_remaining)
122 , DECODE(p_currency_code, FND_API.G_MISS_CHAR, NULL, p_currency_code)
123 , DECODE(p_last_update_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_last_update_date)
124 , DECODE(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by)
125 , DECODE(p_creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_creation_date)
126 , DECODE(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by)
127 , DECODE(p_last_update_login, FND_API.G_MISS_NUM, NULL, p_last_update_login)
128 , DECODE(p_request_id, FND_API.G_MISS_NUM, NULL, p_request_id)
129 , DECODE(p_financial_charge, FND_API.G_MISS_NUM, NULL, p_financial_charge)
130 , DECODE(p_letter_name, FND_API.G_MISS_CHAR, NULL, p_letter_name)
131 , DECODE(p_interest_amt, FND_API.G_MISS_NUM, NULL, p_interest_amt)
132 , DECODE(p_dunning_plan_id, FND_API.G_MISS_NUM, NULL, p_dunning_plan_id)
133 , DECODE(p_contact_destination, FND_API.G_MISS_CHAR, NULL, p_contact_destination)
134 , DECODE(p_contact_party_id, FND_API.G_MISS_NUM, NULL, p_contact_party_id)
135 , DECODE(p_delivery_status, FND_API.G_MISS_CHAR, NULL, p_delivery_status)
136 , DECODE(p_parent_dunning_id, FND_API.G_MISS_NUM, NULL, p_parent_dunning_id)
137 , DECODE(p_dunning_mode, FND_API.G_MISS_CHAR, NULL, p_dunning_mode)
138 , DECODE(p_confirmation_mode, FND_API.G_MISS_CHAR, NULL, p_confirmation_mode)
139 , DECODE(p_org_id, FND_API.G_MISS_NUM, NULL, p_org_id) -- added for bug 9151851
140 , DECODE(p_ag_dn_xref_id, FND_API.G_MISS_NUM, NULL, p_ag_dn_xref_id)
141 , DECODE(p_correspondence_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_correspondence_date)
142 , DECODE(p_addt_xml_template_id, FND_API.G_MISS_NUM, NULL, p_addt_xml_template_id)
143 , DECODE(p_addt_delivery_status, FND_API.G_MISS_CHAR, NULL, p_addt_delivery_status)
144 , DECODE(p_as_of_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_as_of_date)
145 );
146
147 OPEN l_insert;
148 FETCH l_insert INTO px_rowid;
149 IF (l_insert%NOTFOUND) THEN
150 CLOSE l_insert;
151 RAISE NO_DATA_FOUND;
152 END IF;
153 END insert_row;
154
155
156
157 PROCEDURE delete_row(
158 p_dunning_id NUMBER
159 ) IS
160 BEGIN
161 DELETE FROM iex_dunnings
162 WHERE dunning_id = p_dunning_id;
163 IF (SQL%NOTFOUND) THEN
164 RAISE NO_DATA_FOUND;
165 END IF;
166 END delete_row;
167
168
169 PROCEDURE update_row(
170 p_rowid VARCHAR2
171 , p_dunning_id NUMBER
172 , p_template_id NUMBER
173 , p_callback_yn VARCHAR2
174 , p_callback_date DATE
175 , p_campaign_sched_id NUMBER
176 , p_status VARCHAR2
177 , p_delinquency_id NUMBER
178 , p_ffm_request_id NUMBER
179 , p_xml_request_id NUMBER
180 , p_xml_template_id NUMBER
181 , p_object_id NUMBER
182 , p_object_type VARCHAR2
183 , p_dunning_object_id NUMBER
184 , p_dunning_level VARCHAR2
185 , p_dunning_method VARCHAR2
186 , p_amount_due_remaining NUMBER
187 , p_currency_code VARCHAR2
188 , p_last_update_date DATE
189 , p_last_updated_by NUMBER
190 , p_creation_date DATE
191 , p_created_by NUMBER
192 , p_last_update_login NUMBER
193 , p_request_id NUMBER
194 , p_financial_charge NUMBER
195 , p_letter_name VARCHAR2
196 , p_interest_amt NUMBER
197 , p_dunning_plan_id NUMBER
198 , p_contact_destination varchar2
199 , p_contact_party_id NUMBER
200 , p_delivery_status varchar2
201 , p_parent_dunning_id number
202 , p_dunning_mode varchar2 -- added by gnramasa for bug 8489610 14-May-09
203 , p_confirmation_mode varchar2 -- added by gnramasa for bug 8489610 14-May-09
204 , p_ag_dn_xref_id number
205 , p_correspondence_date date
206 , p_addt_xml_template_id number --added for bug 9970624 gnramasa 4th Aug 10
207 , p_addt_delivery_status varchar2 --added for bug 9970624 gnramasa 4th Aug 10
208 ) IS
209 BEGIN
210 UPDATE iex_dunnings
211 SET
212 dunning_id = DECODE(p_dunning_id, FND_API.G_MISS_NUM, NULL, p_dunning_id)
213 , template_id = DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
214 , callback_yn = DECODE(p_callback_yn, FND_API.G_MISS_CHAR, NULL, p_callback_yn)
215 , callback_date = DECODE(p_callback_date, FND_API.G_MISS_DATE, NULL, p_callback_date)
216 , campaign_sched_id = DECODE(p_campaign_sched_id, FND_API.G_MISS_NUM, NULL, p_campaign_sched_id)
217 , status = DECODE(p_status, FND_API.G_MISS_CHAR, NULL, p_status)
218 , delinquency_id = DECODE(p_delinquency_id, FND_API.G_MISS_NUM, NULL, p_delinquency_id)
219 , ffm_request_id = DECODE(p_ffm_request_id, FND_API.G_MISS_NUM, NULL, p_ffm_request_id)
220 , xml_request_id = DECODE(p_xml_request_id, FND_API.G_MISS_NUM, NULL, p_xml_request_id)
221 , xml_template_id = DECODE(p_xml_template_id, FND_API.G_MISS_NUM, NULL, p_xml_template_id)
222 , object_id = DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id)
223 , object_type = DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type)
224 , dunning_object_id = DECODE(p_dunning_object_id, FND_API.G_MISS_NUM, NULL, p_dunning_object_id)
225 , dunning_level = DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
226 , dunning_method = DECODE(p_dunning_method, FND_API.G_MISS_CHAR, NULL, p_dunning_method)
227 , amount_due_remaining = DECODE(p_amount_due_remaining, FND_API.G_MISS_NUM, NULL, p_amount_due_remaining)
228 , currency_code = DECODE(p_currency_code, FND_API.G_MISS_CHAR, NULL, p_currency_code)
229 , last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_last_update_date)
230 , last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,NULL,p_last_updated_by)
231 , creation_date = DECODE(p_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_creation_date)
232 , created_by = DECODE(p_created_by,FND_API.G_MISS_NUM,NULL,p_created_by)
233 , last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,NULL,p_last_update_login)
234 , request_id = DECODE(p_request_id,FND_API.G_MISS_NUM,NULL,p_request_id)
235 , financial_charge = DECODE(p_financial_charge, FND_API.G_MISS_NUM, NULL, p_financial_charge)
236 , letter_name = DECODE(p_letter_name, FND_API.G_MISS_CHAR, NULL, p_letter_name)
237 , interest_amt = DECODE(p_interest_amt, FND_API.G_MISS_NUM, NULL, p_interest_amt)
238 , dunning_plan_id = DECODE(p_dunning_plan_id, FND_API.G_MISS_NUM, NULL, p_dunning_plan_id)
239 , contact_destination = DECODE(p_contact_destination, FND_API.G_MISS_CHAR, NULL, p_contact_destination)
240 , contact_party_id = DECODE(p_contact_party_id, FND_API.G_MISS_NUM, NULL, p_contact_party_id)
241 , delivery_status = DECODE(p_delivery_status, FND_API.G_MISS_CHAR, NULL, p_delivery_status)
242 , parent_dunning_id = DECODE(p_parent_dunning_id, FND_API.G_MISS_NUM, NULL, p_parent_dunning_id)
243 , dunning_mode = DECODE(p_dunning_mode, FND_API.G_MISS_CHAR, NULL, p_dunning_mode)
244 , confirmation_mode = DECODE(p_confirmation_mode, FND_API.G_MISS_CHAR, NULL, p_confirmation_mode)
245 , ag_dn_xref_id = DECODE(p_ag_dn_xref_id, FND_API.G_MISS_NUM, NULL, p_ag_dn_xref_id)
246 , correspondence_date = DECODE(p_correspondence_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_correspondence_date)
247 , addt_xml_template_id = DECODE(p_addt_xml_template_id, FND_API.G_MISS_NUM, NULL, p_addt_xml_template_id)
248 , addt_delivery_status = DECODE(p_addt_delivery_status, FND_API.G_MISS_CHAR, NULL, p_addt_delivery_status)
249 WHERE ROWID = p_rowid;
250 IF (SQL%NOTFOUND) THEN
251 RAISE NO_DATA_FOUND;
252 END IF;
256
253 --End adding for bug 8489610 by gnramasa 14-May-09
254 END update_row;
255
257
258 PROCEDURE lock_row(
259 p_rowid VARCHAR2
260 , p_dunning_id NUMBER
261 , p_template_id NUMBER
262 , p_callback_yn VARCHAR2
263 , p_callback_date DATE
264 , p_campaign_sched_id NUMBER
265 , p_status VARCHAR2
266 , p_delinquency_id NUMBER
267 , p_ffm_request_id NUMBER
268 , p_xml_request_id NUMBER
269 , p_xml_template_id NUMBER
270 , p_object_id NUMBER
271 , p_object_type VARCHAR2
272 , p_dunning_object_id NUMBER
273 , p_dunning_level VARCHAR2
274 , p_dunning_method VARCHAR2
275 , p_amount_due_remaining NUMBER
276 , p_currency_code VARCHAR2
277 , p_last_update_date DATE
278 , p_last_updated_by NUMBER
279 , p_creation_date DATE
280 , p_created_by NUMBER
281 , p_last_update_login NUMBER
282 , p_financial_charge NUMBER
283 , p_letter_name VARCHAR2
284 , p_interest_amt NUMBER
285 , p_dunning_plan_id NUMBER
286 , p_contact_destination varchar2
287 , p_contact_party_id NUMBER
288 ) IS
289 CURSOR l_lock IS
290 SELECT *
291 FROM iex_dunnings
292 WHERE rowid = p_rowid
293 FOR UPDATE OF dunning_id NOWAIT;
294 l_table_rec l_lock%ROWTYPE;
295 BEGIN
296 OPEN l_lock;
297 FETCH l_lock INTO l_table_rec;
298 IF (l_lock%NOTFOUND) THEN
299 CLOSE l_lock;
300 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
301 APP_EXCEPTION.RAISE_EXCEPTION;
302 END IF;
303 CLOSE l_lock;
304 IF (
305 ((l_table_rec.dunning_id = p_dunning_id)
306 OR ((l_table_rec.dunning_id IS NULL)
307 AND ( p_dunning_id IS NULL)))
308 AND ((l_table_rec.template_id = p_template_id)
309 OR ((l_table_rec.template_id IS NULL)
310 AND ( p_template_id IS NULL)))
311 AND ((l_table_rec.callback_yn = p_callback_yn)
312 OR ((l_table_rec.callback_yn IS NULL)
313 AND ( p_callback_yn IS NULL)))
314 AND ((l_table_rec.callback_date = p_callback_date)
315 OR ((l_table_rec.callback_date IS NULL)
316 AND ( p_callback_date IS NULL)))
317 AND ((l_table_rec.campaign_sched_id = p_campaign_sched_id)
318 OR ((l_table_rec.campaign_sched_id IS NULL)
319 AND ( p_campaign_sched_id IS NULL)))
320 AND ((l_table_rec.status = p_status)
321 OR ((l_table_rec.status IS NULL)
322 AND ( p_status IS NULL)))
323 AND ((l_table_rec.delinquency_id = p_delinquency_id)
324 OR ((l_table_rec.delinquency_id IS NULL)
325 AND ( p_delinquency_id IS NULL)))
326 AND ((l_table_rec.ffm_request_id = p_ffm_request_id)
327 OR ((l_table_rec.ffm_request_id IS NULL)
328 AND ( p_ffm_request_id IS NULL)))
329 AND ((l_table_rec.xml_request_id = p_xml_request_id)
330 OR ((l_table_rec.xml_request_id IS NULL)
331 AND ( p_xml_request_id IS NULL)))
332 AND ((l_table_rec.xml_template_id = p_xml_template_id)
333 OR ((l_table_rec.xml_template_id IS NULL)
334 AND ( p_xml_template_id IS NULL)))
335 AND ((l_table_rec.object_id = p_object_id)
336 OR ((l_table_rec.object_id IS NULL)
337 AND ( p_object_id IS NULL)))
338 AND ((l_table_rec.object_type = p_object_type)
339 OR ((l_table_rec.object_type IS NULL)
340 AND ( p_object_type IS NULL)))
341 AND ((l_table_rec.dunning_object_id = p_dunning_object_id)
342 OR ((l_table_rec.dunning_object_id IS NULL)
343 AND ( p_dunning_object_id IS NULL)))
344 AND ((l_table_rec.dunning_level = p_dunning_level)
345 OR ((l_table_rec.dunning_level IS NULL)
346 AND ( p_dunning_level IS NULL)))
347 AND ((l_table_rec.dunning_method = p_dunning_method)
348 OR ((l_table_rec.dunning_method IS NULL)
349 AND ( p_dunning_method IS NULL)))
350 AND ((l_table_rec.amount_due_remaining = p_amount_due_remaining)
351 OR ((l_table_rec.amount_due_remaining IS NULL)
352 AND ( p_amount_due_remaining IS NULL)))
353 AND ((l_table_rec.currency_code = p_currency_code)
354 OR ((l_table_rec.currency_code IS NULL)
355 AND ( p_currency_code IS NULL)))
356 AND ((l_table_rec.last_update_date = p_last_update_date)
357 OR ((l_table_rec.last_update_date IS NULL)
358 AND ( p_last_update_date IS NULL)))
359 AND ((l_table_rec.last_updated_by = p_last_updated_by)
360 OR ((l_table_rec.last_updated_by IS NULL)
361 AND ( p_last_updated_by IS NULL)))
362 AND ((l_table_rec.creation_date = p_creation_date)
363 OR ((l_table_rec.creation_date IS NULL)
364 AND ( p_creation_date IS NULL)))
365 AND ((l_table_rec.created_by = p_created_by)
366 OR ((l_table_rec.created_by IS NULL)
370 AND ( p_last_update_login IS NULL)))
367 AND ( p_created_by IS NULL)))
368 AND ((l_table_rec.last_update_login = p_last_update_login)
369 OR ((l_table_rec.last_update_login IS NULL)
371 AND ((l_table_rec.financial_charge = p_financial_charge)
372 OR ((l_table_rec.financial_charge IS NULL)
373 AND ( p_financial_charge IS NULL)))
374 AND ((l_table_rec.letter_name = p_letter_name)
375 OR ((l_table_rec.letter_name IS NULL)
376 AND ( p_letter_name IS NULL)))
377 AND ((l_table_rec.interest_amt = p_interest_amt)
378 OR ((l_table_rec.interest_amt IS NULL)
379 AND ( p_interest_amt IS NULL)))
380 AND ((l_table_rec.dunning_plan_id = p_dunning_plan_id)
381 OR ((l_table_rec.dunning_plan_id IS NULL)
382 AND ( p_dunning_plan_id IS NULL)))
383 AND ((l_table_rec.contact_destination = p_contact_destination)
384 OR ((l_table_rec.contact_destination IS NULL)
385 AND ( p_contact_destination IS NULL)))
386 AND ((l_table_rec.contact_party_id = p_contact_party_id)
387 OR ((l_table_rec.contact_party_id IS NULL)
388 AND ( p_contact_party_id IS NULL)))
389 ) THEN
390 RETURN;
391 ELSE
392 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
393 APP_EXCEPTION.RAISE_EXCEPTION;
394 END IF;
395 END lock_row;
396
397 PROCEDURE insert_Staged_Dunning_row(
398 px_rowid IN OUT NOCOPY VARCHAR2
399 , px_dunning_trx_id IN OUT NOCOPY NUMBER
400 , p_dunning_id NUMBER
401 , p_cust_trx_id NUMBER
402 , p_payment_schedule_id NUMBER
403 , p_ag_dn_xref_id NUMBER
404 , p_stage_number NUMBER
405 , p_created_by NUMBER
406 , p_creation_date DATE
407 , p_last_updated_by NUMBER
408 , p_last_update_date DATE
409 , p_last_update_login NUMBER
410 , p_object_version_number NUMBER
411 ) IS
412 CURSOR l_insert IS
413 SELECT ROWID
414 FROM iex_dunning_transactions
415 WHERE dunning_trx_id = px_dunning_trx_id;
416 --
417 CURSOR get_seq_csr is
418 SELECT iex_dunning_transactions_s.nextval
419 FROM sys.dual;
420 BEGIN
421 --
422 If (px_dunning_trx_id IS NULL) OR (px_dunning_trx_id = FND_API.G_MISS_NUM) then
423 OPEN get_seq_csr;
424 FETCH get_seq_csr INTO px_dunning_trx_id;
425 CLOSE get_seq_csr;
426 End If;
427 --Start adding for bug 8489610 by gnramasa 14-May-09
428 --
429 INSERT INTO IEX_DUNNING_TRANSACTIONS (
430 DUNNING_TRX_ID
431 , DUNNING_ID
432 , CUST_TRX_ID
433 , PAYMENT_SCHEDULE_ID
434 , AG_DN_XREF_ID
435 , STAGE_NUMBER
436 , CREATED_BY
437 , CREATION_DATE
438 , LAST_UPDATED_BY
439 , LAST_UPDATE_DATE
440 , LAST_UPDATE_LOGIN
441 , OBJECT_VERSION_NUMBER
442 ) VALUES (
443 px_dunning_trx_id
444 , DECODE(p_dunning_id, FND_API.G_MISS_NUM, NULL, p_dunning_id)
445 , DECODE(p_cust_trx_id, FND_API.G_MISS_NUM, NULL, p_cust_trx_id)
446 , DECODE(p_payment_schedule_id, FND_API.G_MISS_NUM, NULL, p_payment_schedule_id)
447 , DECODE(p_ag_dn_xref_id, FND_API.G_MISS_NUM, NULL, p_ag_dn_xref_id)
448 , DECODE(p_stage_number, FND_API.G_MISS_NUM, NULL, p_stage_number)
449 , DECODE(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by)
450 , DECODE(p_creation_date, FND_API.G_MISS_DATE, NULL, p_creation_date)
451 , DECODE(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by)
452 , DECODE(p_last_update_date, FND_API.G_MISS_DATE, NULL, p_last_update_date)
453 , DECODE(p_last_update_login, FND_API.G_MISS_NUM, NULL, p_last_update_login)
454 , DECODE(p_object_version_number, FND_API.G_MISS_NUM, NULL, p_object_version_number)
455 );
456
457 OPEN l_insert;
458 FETCH l_insert INTO px_rowid;
459 IF (l_insert%NOTFOUND) THEN
460 CLOSE l_insert;
461 RAISE NO_DATA_FOUND;
462 END IF;
463 END insert_Staged_Dunning_row;
464
465 /*
466 PROCEDURE update_Staged_Dunning_row(
467 p_rowid VARCHAR2
468 , p_dunning_id NUMBER
469 , p_template_id NUMBER
470 , p_callback_yn VARCHAR2
471 , p_callback_date DATE
472 , p_campaign_sched_id NUMBER
473 , p_status VARCHAR2
474 , p_delinquency_id NUMBER
475 , p_ffm_request_id NUMBER
476 , p_xml_request_id NUMBER
477 , p_xml_template_id NUMBER
478 , p_object_id NUMBER
479 , p_object_type VARCHAR2
480 , p_dunning_object_id NUMBER
481 , p_dunning_level VARCHAR2
482 , p_dunning_method VARCHAR2
483 , p_amount_due_remaining NUMBER
484 , p_currency_code VARCHAR2
485 , p_last_update_date DATE
486 , p_last_updated_by NUMBER
487 , p_creation_date DATE
488 , p_created_by NUMBER
492 , p_letter_name VARCHAR2
489 , p_last_update_login NUMBER
490 , p_request_id NUMBER
491 , p_financial_charge NUMBER
493 , p_interest_amt NUMBER
494 , p_dunning_plan_id NUMBER
495 , p_contact_destination varchar2
496 , p_contact_party_id NUMBER
497 , p_delivery_status varchar2
498 , p_parent_dunning_id number
499 , p_dunning_mode varchar2 -- added by gnramasa for bug 8489610 14-May-09
500 , p_confirmation_mode varchar2 -- added by gnramasa for bug 8489610 14-May-09
501 , p_dunning_plan_line_id number
502 ) IS
503 BEGIN
504 UPDATE iex_dunnings
505 SET
506 dunning_id = DECODE(p_dunning_id, FND_API.G_MISS_NUM, NULL, p_dunning_id)
507 , template_id = DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
508 , callback_yn = DECODE(p_callback_yn, FND_API.G_MISS_CHAR, NULL, p_callback_yn)
509 , callback_date = DECODE(p_callback_date, FND_API.G_MISS_DATE, NULL, p_callback_date)
510 , campaign_sched_id = DECODE(p_campaign_sched_id, FND_API.G_MISS_NUM, NULL, p_campaign_sched_id)
511 , status = DECODE(p_status, FND_API.G_MISS_CHAR, NULL, p_status)
512 , delinquency_id = DECODE(p_delinquency_id, FND_API.G_MISS_NUM, NULL, p_delinquency_id)
513 , ffm_request_id = DECODE(p_ffm_request_id, FND_API.G_MISS_NUM, NULL, p_ffm_request_id)
514 , xml_request_id = DECODE(p_xml_request_id, FND_API.G_MISS_NUM, NULL, p_xml_request_id)
515 , xml_template_id = DECODE(p_xml_template_id, FND_API.G_MISS_NUM, NULL, p_xml_template_id)
516 , object_id = DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id)
517 , object_type = DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type)
518 , dunning_object_id = DECODE(p_dunning_object_id, FND_API.G_MISS_NUM, NULL, p_dunning_object_id)
519 , dunning_level = DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
520 , dunning_method = DECODE(p_dunning_method, FND_API.G_MISS_CHAR, NULL, p_dunning_method)
521 , amount_due_remaining = DECODE(p_amount_due_remaining, FND_API.G_MISS_NUM, NULL, p_amount_due_remaining)
522 , currency_code = DECODE(p_currency_code, FND_API.G_MISS_CHAR, NULL, p_currency_code)
523 , last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_last_update_date)
524 , last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,NULL,p_last_updated_by)
525 , creation_date = DECODE(p_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_creation_date)
526 , created_by = DECODE(p_created_by,FND_API.G_MISS_NUM,NULL,p_created_by)
527 , last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,NULL,p_last_update_login)
528 , request_id = DECODE(p_request_id,FND_API.G_MISS_NUM,NULL,p_request_id)
529 , financial_charge = DECODE(p_financial_charge, FND_API.G_MISS_NUM, NULL, p_financial_charge)
530 , letter_name = DECODE(p_letter_name, FND_API.G_MISS_CHAR, NULL, p_letter_name)
531 , interest_amt = DECODE(p_interest_amt, FND_API.G_MISS_NUM, NULL, p_interest_amt)
532 , dunning_plan_id = DECODE(p_dunning_plan_id, FND_API.G_MISS_NUM, NULL, p_dunning_plan_id)
533 , contact_destination = DECODE(p_contact_destination, FND_API.G_MISS_CHAR, NULL, p_contact_destination)
534 , contact_party_id = DECODE(p_contact_party_id, FND_API.G_MISS_NUM, NULL, p_contact_party_id)
535 , delivery_status = DECODE(p_delivery_status, FND_API.G_MISS_CHAR, NULL, p_delivery_status)
536 , parent_dunning_id = DECODE(p_parent_dunning_id, FND_API.G_MISS_NUM, NULL, p_parent_dunning_id)
537 , dunning_mode = DECODE(p_dunning_mode, FND_API.G_MISS_CHAR, NULL, p_dunning_mode)
538 , confirmation_mode = DECODE(p_confirmation_mode, FND_API.G_MISS_CHAR, NULL, p_confirmation_mode)
539 , dunning_plan_line_id = DECODE(p_dunning_plan_line_id, FND_API.G_MISS_CHAR, NULL, p_dunning_plan_line_id)
540 WHERE ROWID = p_rowid;
541 IF (SQL%NOTFOUND) THEN
542 RAISE NO_DATA_FOUND;
543 END IF;
544 --End adding for bug 8489610 by gnramasa 14-May-09
545 END update_Staged_Dunning_row;
546 */
547
548 BEGIN
549 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
550
551 END iex_dunnings_pkg;