[Home] [Help]
PACKAGE BODY: APPS.IEX_AG_DN_PKG
Source
1 PACKAGE BODY IEX_AG_DN_pkg AS
2 /* $Header: iextadub.pls 120.2 2004/11/30 21:08:12 clchang ship $ */
3
4 PG_DEBUG NUMBER(2) ;
5
6 PROCEDURE insert_row(
7 px_rowid IN OUT NOCOPY VARCHAR2
8 , px_ag_dn_xref_id IN OUT NOCOPY NUMBER
9 , p_aging_bucket_id NUMBER
10 , p_aging_bucket_line_id NUMBER
11 , p_callback_flag VARCHAR2
12 , p_callback_days NUMBER
13 , p_FM_METHOD VARCHAR2
14 , p_template_id NUMBER
15 , p_xdo_template_id NUMBER
16 , p_score_RANGE_LOW NUMBER
17 , p_score_RANGE_HIGH NUMBER
18 , p_dunning_level VARCHAR2
19 , p_object_version_number NUMBER
20 , p_last_update_date DATE
21 , p_last_updated_by NUMBER
22 , p_creation_date DATE
23 , p_created_by NUMBER
24 , p_last_update_login NUMBER
25 ) IS
26 CURSOR l_insert IS
27 SELECT ROWID
28 FROM iex_ag_dn_xref
29 WHERE ag_dn_xref_id = px_ag_dn_xref_id;
30 --
31 CURSOR get_seq_csr is
32 SELECT IEX_AG_DN_XREF_S.nextval
33 FROM sys.dual;
34 BEGIN
35 --
36 If (px_ag_dn_xref_ID IS NULL) OR (px_ag_dn_xref_ID = FND_API.G_MISS_NUM) then
37 OPEN get_seq_csr;
38 FETCH get_seq_csr INTO px_ag_dn_xref_ID;
39 CLOSE get_seq_csr;
40 End If;
41 --
42 INSERT INTO IEX_AG_DN_XREF (
43 AG_DN_XREF_ID
44 , AGING_BUCKET_ID
45 , AGING_BUCKET_LINE_ID
46 , CALLBACK_FLAG
47 , CALLBACK_DAYS
48 , FM_METHOD
49 , TEMPLATE_ID
50 , XDO_TEMPLATE_ID
51 , SCORE_RANGE_LOW
52 , SCORE_RANGE_HIGH
53 , DUNNING_LEVEL
54 , OBJECT_VERSION_NUMBER
55 , last_update_date
56 , last_updated_by
57 , creation_date
58 , created_by
59 , last_update_login
60 ) VALUES (
61 px_ag_dn_xref_id
62 , DECODE(p_aging_bucket_id, FND_API.G_MISS_NUM, NULL, p_aging_bucket_id)
63 , DECODE(p_aging_bucket_line_id, FND_API.G_MISS_NUM, NULL, p_aging_bucket_line_id)
64 , DECODE(p_callback_flag, FND_API.G_MISS_CHAR, NULL, p_callback_flag)
65 , DECODE(p_callback_days, FND_API.G_MISS_NUM, NULL, p_callback_days)
66 , DECODE(p_fm_method, FND_API.G_MISS_CHAR, NULL, p_fm_method)
67 , DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
68 , DECODE(p_xdo_template_id, FND_API.G_MISS_NUM, NULL, p_xdo_template_id)
69 , DECODE(p_score_range_low, FND_API.G_MISS_NUM, NULL, p_score_range_low)
70 , DECODE(p_score_range_high, FND_API.G_MISS_NUM, NULL, p_score_range_high)
71 , DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
72 , DECODE(p_object_version_number, FND_API.G_MISS_NUM, NULL, p_object_version_number)
73 , DECODE(p_last_update_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_last_update_date)
74 , DECODE(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by)
75 , DECODE(p_creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_creation_date)
76 , DECODE(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by)
77 , DECODE(p_last_update_login, FND_API.G_MISS_NUM, NULL, p_last_update_login)
78 );
79
80 OPEN l_insert;
81 FETCH l_insert INTO px_rowid;
82 IF (l_insert%NOTFOUND) THEN
83 CLOSE l_insert;
84 RAISE NO_DATA_FOUND;
85 END IF;
86 END insert_row;
87
88
89
90 PROCEDURE delete_row(
91 p_ag_dn_xref_id NUMBER
92 ) IS
93 BEGIN
94 DELETE FROM iex_ag_dn_xref
95 WHERE ag_dn_xref_id = p_ag_dn_xref_id;
96 IF (SQL%NOTFOUND) THEN
97 RAISE NO_DATA_FOUND;
98 END IF;
99 END delete_row;
100
101
102 PROCEDURE update_row(
103 p_rowid VARCHAR2
104 , p_ag_dn_xref_id NUMBER
105 , p_aging_bucket_id NUMBER
106 , p_aging_bucket_line_id NUMBER
107 , p_callback_flag VARCHAR2
108 , p_callback_days NUMBER
109 , p_FM_METHOD VARCHAR2
110 , p_template_id NUMBER
111 , p_xdo_template_id NUMBER
112 , p_score_RANGE_LOW NUMBER
113 , p_score_RANGE_HIGH NUMBER
114 , p_dunning_level VARCHAR2
115 , p_object_version_number NUMBER
116 , p_last_update_date DATE
117 , p_last_updated_by NUMBER
118 , p_creation_date DATE
119 , p_created_by NUMBER
120 , p_last_update_login NUMBER
121 ) IS
122 BEGIN
123 UPDATE iex_ag_dn_xref
124 SET
125 ag_dn_xref_id = DECODE(p_ag_dn_xref_id, FND_API.G_MISS_NUM, NULL, p_ag_dn_xref_id)
126 , aging_bucket_id = DECODE(p_aging_bucket_id, FND_API.G_MISS_NUM, NULL, p_aging_bucket_id)
127 , aging_bucket_line_id = DECODE(p_aging_bucket_line_id, FND_API.G_MISS_NUM, NULL, p_aging_bucket_line_id)
128 , callback_flag = DECODE(p_callback_flag, FND_API.G_MISS_CHAR, NULL, p_callback_flag)
129 , callback_days = DECODE(p_callback_days, FND_API.G_MISS_NUM, NULL, p_callback_days)
130 , fm_method = DECODE(p_fm_method, FND_API.G_MISS_CHAR, NULL, p_fm_method)
131 , template_id = DECODE(p_template_id, FND_API.G_MISS_NUM, NULL, p_template_id)
132 , xdo_template_id = DECODE(p_xdo_template_id, FND_API.G_MISS_NUM, NULL, p_xdo_template_id)
133 , score_range_low = DECODE(p_score_range_low, FND_API.G_MISS_NUM, NULL, p_score_range_low)
134 , score_range_high = DECODE(p_score_range_high, FND_API.G_MISS_NUM, NULL, p_score_range_high)
135 , dunning_level = DECODE(p_dunning_level, FND_API.G_MISS_CHAR, NULL, p_dunning_level)
136 , object_version_number = DECODE(p_object_version_number, FND_API.G_MISS_NUM, NULL, p_object_version_number)
137 , last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_last_update_date)
138 , last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,NULL,p_last_updated_by)
139 , creation_date = DECODE(p_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_creation_date)
140 , created_by = DECODE(p_created_by,FND_API.G_MISS_NUM,NULL,p_created_by)
141 , last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,NULL,p_last_update_login)
142 WHERE ROWID = p_rowid;
143 IF (SQL%NOTFOUND) THEN
144 RAISE NO_DATA_FOUND;
145 END IF;
146 END update_row;
147
148
149
150 PROCEDURE lock_row(
151 p_rowid VARCHAR2
152 , p_ag_dn_xref_id NUMBER
153 , p_aging_bucket_id NUMBER
154 , p_aging_bucket_line_id NUMBER
155 , p_callback_flag VARCHAR2
156 , p_callback_days NUMBER
157 , p_FM_METHOD VARCHAR2
158 , p_template_id NUMBER
159 , p_xdo_template_id NUMBER
160 , p_score_RANGE_LOW NUMBER
161 , p_score_RANGE_HIGH NUMBER
162 , p_dunning_level VARCHAR2
163 , p_object_version_number NUMBER
164 , p_last_update_date DATE
165 , p_last_updated_by NUMBER
166 , p_creation_date DATE
167 , p_created_by NUMBER
168 , p_last_update_login NUMBER
169 ) IS
170 CURSOR l_lock IS
171 SELECT *
172 FROM iex_ag_dn_xref
173 WHERE rowid = p_rowid
174 FOR UPDATE OF ag_dn_xref_id NOWAIT;
175 l_table_rec l_lock%ROWTYPE;
176 BEGIN
177 OPEN l_lock;
178 FETCH l_lock INTO l_table_rec;
179 IF (l_lock%NOTFOUND) THEN
180 CLOSE l_lock;
181 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
182 APP_EXCEPTION.RAISE_EXCEPTION;
183 END IF;
184 CLOSE l_lock;
185 IF (
186 ((l_table_rec.ag_dn_xref_id = p_ag_dn_xref_id)
187 OR ((l_table_rec.ag_dn_xref_id IS NULL)
188 AND ( p_ag_dn_xref_id IS NULL)))
189 AND ((l_table_rec.aging_bucket_id = p_aging_bucket_id)
190 OR ((l_table_rec.aging_bucket_id IS NULL)
191 AND ( p_aging_bucket_id IS NULL)))
192 AND ((l_table_rec.aging_bucket_line_id = p_aging_bucket_line_id)
193 OR ((l_table_rec.aging_bucket_line_id IS NULL)
194 AND ( p_aging_bucket_line_id IS NULL)))
195 AND ((l_table_rec.callback_flag = p_callback_flag)
196 OR ((l_table_rec.callback_flag IS NULL)
197 AND ( p_callback_flag IS NULL)))
198 AND ((l_table_rec.callback_days = p_callback_days)
199 OR ((l_table_rec.callback_days IS NULL)
200 AND ( p_callback_days IS NULL)))
201 AND ((l_table_rec.fm_method = p_fm_method)
202 OR ((l_table_rec.fm_method IS NULL)
203 AND ( p_fm_method IS NULL)))
204 AND ((l_table_rec.template_id = p_template_id)
205 OR ((l_table_rec.template_id IS NULL)
206 AND ( p_template_id IS NULL)))
207 AND ((l_table_rec.xdo_template_id = p_xdo_template_id)
208 OR ((l_table_rec.xdo_template_id IS NULL)
209 AND ( p_xdo_template_id IS NULL)))
210 AND ((l_table_rec.score_range_low = p_score_range_low)
211 OR ((l_table_rec.score_range_low IS NULL)
212 AND ( p_score_range_low IS NULL)))
213 AND ((l_table_rec.score_range_high = p_score_range_high)
214 OR ((l_table_rec.score_range_high IS NULL)
215 AND ( p_score_range_high IS NULL)))
216 AND ((l_table_rec.dunning_level = p_dunning_level)
217 OR ((l_table_rec.dunning_level IS NULL)
218 AND ( p_dunning_level IS NULL)))
219 AND ((l_table_rec.object_version_number = p_object_version_number)
220 OR ((l_table_rec.object_version_number IS NULL)
221 AND ( p_object_version_number IS NULL)))
222 AND ((l_table_rec.last_update_date = p_last_update_date)
223 OR ((l_table_rec.last_update_date IS NULL)
224 AND ( p_last_update_date IS NULL)))
225 AND ((l_table_rec.last_updated_by = p_last_updated_by)
226 OR ((l_table_rec.last_updated_by IS NULL)
227 AND ( p_last_updated_by IS NULL)))
228 AND ((l_table_rec.creation_date = p_creation_date)
229 OR ((l_table_rec.creation_date IS NULL)
230 AND ( p_creation_date IS NULL)))
231 AND ((l_table_rec.created_by = p_created_by)
232 OR ((l_table_rec.created_by IS NULL)
233 AND ( p_created_by IS NULL)))
234 AND ((l_table_rec.last_update_login = p_last_update_login)
235 OR ((l_table_rec.last_update_login IS NULL)
236 AND ( p_last_update_login IS NULL)))
237 ) THEN
238 RETURN;
239 ELSE
240 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 END IF;
243 END lock_row;
244
245 BEGIN
246 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
247
248 END iex_ag_dn_pkg;