4 /*=======================================================================+
1 PACKAGE BODY ARP_CORRECT_CCID AS
2 /* $Header: ARCCCIDB.pls 120.2 2005/07/22 00:45:12 hyu noship $ */
3
5 | Package Global Constants
6 +=======================================================================*/
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
8
9 /*========================================================================
10 | PUBLIC PROCEDURE Correct_Lines_CCID
11 |
12 | DESCRIPTION
13 | This procedure will correct all the specific lines that have been
14 | choosen for correction.
15 |
16 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
17 | Enter a list of all local procedures and functions which
18 | are call this package.
19 |
20 |
21 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
22 | Enter a list of all local procedures and cuntions which
23 | this package calls.
24 |
25 | PARAMETERS
26 | p_distribution_id IN This will be the primary key of
27 | the table we will be updating.
28 | p_old_ccid IN This is the CCID which is invalid and
29 | must be replaced.
30 | p_new_ccid IN This is the CCID that the user has
31 | choosen to replace the invalid CCID
32 | p_category_type IN Type of trx we are processing
33 | p_dist_type IN Distribution Type
34 | p_parent_id IN primary key of parent table
35 | p_source_table IN Code for parent id source table.
36 |
37 | KNOWN ISSUES
38 | Enter business functionality which was de-scoped as part of the
39 | implementation. Ideally this should never be used.
40 |
41 | NOTES
42 | Any interesting aspect of the code in the package body which needs
43 | to be stated.
44 |
45 | MODIFICATION HISTORY
46 | Date Author Description of Changes
47 | 10-Nov-2003 Debbie Sue Jancis Created
48 *=======================================================================*/
49 PROCEDURE Correct_Lines_CCID ( p_distribution_id IN NUMBER,
50 p_old_ccid IN NUMBER,
51 p_new_ccid IN NUMBER,
52 p_category_type IN VARCHAR2,
53 p_dist_type IN VARCHAR2,
54 p_parent_id IN NUMBER,
55 p_source_table IN VARCHAR2 ) IS
56
57 /*-----------------------------------------------------------------------+
58 | Local Variable Declarations and initializations |
59 +-----------------------------------------------------------------------*/
60
61 BEGIN
62
63 IF PG_DEBUG in ('Y', 'C') THEN
64 arp_standard.debug('ARP_CORRECT_CCID.Correct_Lines_CCID()+');
65 arp_standard.debug(' p_distribution_id :' || to_char(p_distribution_id));
66 arp_standard.debug(' p_old_ccid :' || to_char(p_old_ccid));
67 arp_standard.debug(' p_new_ccid :' || to_char(p_new_ccid));
68 arp_standard.debug(' p_category_type :' || p_category_type);
69 arp_standard.debug(' p_dist_type:' || p_dist_type);
70 arp_standard.debug(' p_parent_id:' || to_char(p_parent_id));
71 arp_standard.debug(' p_source_table: ' || p_source_table);
72 END IF;
73
74 /* should we initialize arp_global before use?? */
75 -- arp_global.init;
76
77 /* based upon parameters coming in, we have to update specific tables
78 to correct the CCID. The tables will be determined by several
79 Columns */
80
81 /* if we are dealing with GL DIST Records */
85 UPDATE RA_CUST_TRX_LINE_GL_DIST
82 IF (p_source_table = 'GLD') THEN
83
84 /* update the distribution record */
86 SET code_combination_id = nvl(p_new_ccid, code_combination_id),
87 last_update_date = SYSDATE,
88 last_updated_by = arp_global.last_updated_by,
89 last_update_login = arp_global.last_update_login
90 WHERE
91 cust_trx_line_gl_dist_id = p_distribution_id
92 AND code_combination_id = p_old_ccid;
93
94
95 ELSE
96
97 /* we need to update ar_distributions */
98
99 update AR_DISTRIBUTIONS
100 SET code_combination_id = NVL( p_new_ccid, code_combination_id),
101 last_update_date = sysdate,
102 last_updated_by = arp_global.last_updated_by,
103 last_update_login = arp_global.last_update_login
104 WHERE code_combination_id = p_old_ccid
105 and line_id = p_distribution_id;
106
107 IF ( p_source_table = 'ADJ' and p_dist_type = 'ADJ' ) THEN
108 /* We need to update the parent record if the distribution
109 type is ADJ */
110
111 update AR_ADJUSTMENTS
112 SET code_combination_id = NVL(p_new_ccid, code_combination_id),
113 last_update_date = sysdate,
114 last_updated_by = arp_global.last_updated_by,
115 last_update_login = arp_global.last_update_login
116 WHERE adjustment_id = p_parent_id
117 AND code_combination_id = p_old_ccid;
118 END IF;
119
120 IF (p_source_table = 'MCD' and p_dist_type = 'MISCCASH') then
121 UPDATE AR_MISC_CASH_DISTRIBUTIONS
122 SET code_combination_id = NVL(p_new_ccid, code_combination_id),
123 last_update_date = SYSDATE,
124 last_updated_by = arp_global.last_updated_by,
125 last_update_login = arp_global.last_update_login
126 where misc_cash_distribution_id = p_parent_id
127 and code_combination_id = p_old_ccid;
128 END IF;
129
130 IF (p_source_table = 'CRH' and p_dist_type ='CASH') THEN
131 UPDATE AR_CASH_RECEIPT_HISTORY
132 SET account_code_combination_id = NVL(p_new_ccid,
133 account_code_combination_id),
134 last_update_date = SYSDATE,
135 last_updated_by = arp_global.last_updated_by,
136 last_update_login = arp_global.last_update_login
137 WHERE account_code_combination_id = p_old_ccid
138 AND cash_Receipt_history_id = p_parent_id
139 AND current_record_flag = 'Y';
140 END IF;
141 END IF;
142
143 /* delete from the interim table */
144 DELETE FROM AR_CCID_CORRECTIONS
145 WHERE code_combination_id = p_old_ccid
146 AND distribution_type = p_dist_type
147 AND category_type = p_category_type
148 AND distribution_id = p_distribution_id
149 AND source_table = p_source_table
150 AND submission_id IS NULL;
151
152 IF PG_DEBUG in ('Y', 'C') THEN
153 arp_standard.debug('ARP_CORRECT_CCID.Correct_Lines_CCID()-');
154 END IF;
155
156 EXCEPTION
157
158 WHEN OTHERS THEN
159 IF PG_DEBUG in ('Y', 'C') THEN
160 arp_standard.debug('ARP_CORRECT_CCID.Correct_Lines_CCID()');
161 END IF;
162 RAISE;
163
164 END Correct_Lines_CCID;
165
166 /*========================================================================
167 | PUBLIC PROCEDURE lock_and_update
168 |
169 | DESCRIPTION
170 | This procedure will take an invalid CCID and lock and update all
171 | rows in the ar_ccid_corrections table.
172 |
173 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
174 | This is called from the form ARXGLCOR.fmb
175 |
176 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
177 |
178 | PARAMETERS
179 | p_old_ccid IN OLD CCID
180 | p_new_ccid_id IN NEW CCID to be replaced.
181 |
182 | KNOWN ISSUES
183 | none
184 |
185 | NOTES
186 |
187 | MODIFICATION HISTORY
188 | Date Author Description of Changes
189 | 17-Nov-2003 Debbie Sue Jancis Created
190 *=======================================================================*/
191 PROCEDURE lock_and_update ( p_old_ccid IN NUMBER,
192 p_new_ccid IN NUMBER,
193 p_category_type IN VARCHAR2,
194 p_dist_type IN VARCHAR2,
195 p_seq_id IN NUMBER) IS
196
197 l_status_code CONSTANT VARCHAR2(20) := 'IN_PROGRESS';
198
199 BEGIN
200 IF PG_DEBUG in ('Y', 'C') THEN
201 arp_standard.debug(' ARP_CORRECT_CCID.lock_and_update()+');
202 arp_standard.debug(' p_old_ccid :' || to_char(p_old_ccid));
203 arp_standard.debug(' p_new_ccid :' || to_char(p_new_ccid));
204 arp_standard.debug(' p_category_type :' || p_category_type);
205 arp_standard.debug(' p_dist_type:' || p_dist_type);
206 END IF;
207
208 /*---------------------------------------------------------------------+
209 | Undate rows with the new CCID based on the old ccid, trx code and |
210 | distribution code. |
211 +---------------------------------------------------------------------*/
212
213 Update AR_CCID_CORRECTIONS
214 set NEW_CODE_COMBINATION_ID = p_new_ccid,
215 submission_id = p_seq_id
216 WHERE
217 code_combination_id = p_old_ccid and
221 IF PG_DEBUG in ('Y', 'C') THEN
218 distribution_type = p_dist_type and
219 category_type = p_category_type;
220
222 arp_standard.debug(' ARP_CORRECT_CCID.lock_and_update()-');
223 END IF;
224
225 EXCEPTION
226
227 WHEN OTHERS THEN
228 IF PG_DEBUG in ('Y', 'C') THEN
229 arp_standard.debug('EXCEPTION: ARP_CORRECT_CCID.lock_and_update()');
230 END IF;
231 RAISE;
232
233 END lock_and_update;
234
235
236 /*========================================================================
237 | PUBLIC PROCEDURE Correct_All_Invalid_CCID
238 |
239 | DESCRIPTION
240 | This procedure will take an invalid CCID and do a global replacement
241 | in all tables, with a new Valid CCID in order to enable records
242 | to post.
243 |
244 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
245 | Enter a list of all local procedures and functions which
246 | are call this package.
247 |
248 |
249 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
250 |
251 | PARAMETERS
252 | p_submission_id IN Unique identifier for all rows in
253 | this particular submission
254 |
255 | KNOWN ISSUES
256 | Enter business functionality which was de-scoped as part of the
257 | implementation. Ideally this should never be used.
258 |
259 | NOTES
260 | Any interesting aspect of the code in the package body which needs
261 | to be stated.
262 |
263 | MODIFICATION HISTORY
264 | Date Author Description of Changes
265 | 10-Nov-2003 Debbie Sue Jancis Created
266 *=======================================================================*/
267 PROCEDURE Correct_All_Invalid_CCID(p_errbuff OUT NOCOPY varchar2,
268 p_retcode OUT NOCOPY number,
269 p_submission_id IN NUMBER) IS
270
271 BEGIN
272 IF PG_DEBUG in ('Y', 'C') THEN
273 arp_standard.debug(' ARP_CORRECT_CCID.Correct_All_Invalid_CCID()+');
274 arp_standard.debug(' p_submission_id :' || p_submission_id);
275 END IF;
276
277 /* Correct GL_DIST ccids */
278 UPDATE RA_CUST_TRX_LINE_GL_DIST gld
279 SET last_update_date = SYSDATE,
280 last_updated_by = arp_global.last_updated_by,
281 last_update_login = arp_global.last_update_login,
282 code_combination_id = (
283 SELECT nvl(new_code_combination_id, code_combination_id)
284 FROM ar_ccid_corrections acc
285 WHERE acc.submission_id = p_submission_id
286 AND acc.source_table = 'GLD'
287 AND acc.distribution_type = gld.account_class
288 AND acc.distribution_id = gld.cust_trx_line_gl_dist_id)
289 WHERE gld.cust_trx_line_gl_dist_id in (
290 SELECT distribution_id
291 FROM ar_ccid_corrections
292 WHERE submission_id = p_submission_id
293 AND source_table = 'GLD');
294
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_standard.debug(' ra_cust_trx_line_gl_dist rows updated: ' ||
297 SQL%ROWCOUNT);
298 END IF;
299
300 /* Correct AR_DISTRIBUTION ccids */
301 UPDATE AR_DISTRIBUTIONS ard
302 SET last_update_date = SYSDATE,
303 last_updated_by = arp_global.last_updated_by,
304 last_update_login = arp_global.last_update_login,
305 code_combination_id = (
306 SELECT nvl(new_code_combination_id, code_combination_id)
307 FROM ar_ccid_corrections acc
308 WHERE acc.submission_id = p_submission_id
309 AND acc.distribution_id = ard.line_id
310 AND acc.source_table = ard.source_table
311 AND acc.distribution_type = ard.source_type)
312 WHERE ard.line_id in (
313 SELECT distribution_id
314 FROM ar_ccid_corrections
315 WHERE submission_id = p_submission_id
316 AND source_table IN ('ADJ','CRH','RA','MCD','TH'));
317
318 IF PG_DEBUG in ('Y', 'C') THEN
319 arp_standard.debug(' ar_distribution rows updated: ' ||
320 SQL%ROWCOUNT);
321 END IF;
322
323 /* correct parent ADJ records */
324 UPDATE AR_ADJUSTMENTS adj
325 SET last_update_date = SYSDATE,
326 last_updated_by = arp_global.last_updated_by,
327 last_update_login = arp_global.last_update_login,
328 code_combination_id = (
329 SELECT nvl(new_code_combination_id, code_combination_id)
330 FROM ar_ccid_corrections acc
331 WHERE acc.submission_id = p_submission_id
332 AND acc.source_table = 'ADJ'
333 AND acc.distribution_type = 'ADJ'
334 AND acc.parent_id = adj.adjustment_id)
335 WHERE adj.adjustment_id in (
336 SELECT parent_id
337 FROM ar_ccid_corrections
338 WHERE submission_id = p_submission_id
339 AND source_table = 'ADJ'
340 AND distribution_type = 'ADJ');
341
342 IF PG_DEBUG in ('Y', 'C') THEN
343 arp_standard.debug(' ar_adjustments rows updated: ' ||
344 SQL%ROWCOUNT);
345 END IF;
346
347 /* correct parent CRH records */
348 UPDATE AR_CASH_RECEIPT_HISTORY crh
349 SET last_update_date = SYSDATE,
350 last_updated_by = arp_global.last_updated_by,
351 last_update_login = arp_global.last_update_login,
352 account_code_combination_id = (
353 SELECT nvl(new_code_combination_id, account_code_combination_id)
354 FROM ar_ccid_corrections acc
355 WHERE acc.submission_id = p_submission_id
356 AND acc.source_table = 'CRH'
357 AND acc.distribution_type = 'CASH'
358 AND acc.parent_id = crh.cash_receipt_history_id)
359 WHERE crh.cash_receipt_history_id in (
360 SELECT parent_id
361 FROM ar_ccid_corrections
362 WHERE submission_id = p_submission_id
363 AND source_table = 'CRH'
364 AND distribution_type = 'CASH');
365
366 IF PG_DEBUG in ('Y', 'C') THEN
367 arp_standard.debug(' ar_cash_receipt_history rows updated: ' ||
368 SQL%ROWCOUNT);
369 END IF;
370
371 /* correct parent MCD records */
372 UPDATE AR_MISC_CASH_DISTRIBUTIONS mcd
373 SET last_update_date = SYSDATE,
374 last_updated_by = arp_global.last_updated_by,
375 last_update_login = arp_global.last_update_login,
376 code_combination_id = (
377 SELECT nvl(new_code_combination_id, code_combination_id)
378 FROM ar_ccid_corrections acc
379 WHERE acc.submission_id = p_submission_id
380 AND acc.source_table = 'MCD'
381 AND acc.distribution_type = 'MISCCASH'
382 AND acc.parent_id = mcd.misc_cash_distribution_id)
383 WHERE misc_cash_distribution_id in (
384 SELECT parent_id
385 FROM ar_ccid_corrections
386 WHERE submission_id = p_submission_id
387 AND source_table = 'MCD'
388 AND distribution_type = 'MISCCASH');
389
390 IF PG_DEBUG in ('Y', 'C') THEN
391 arp_standard.debug(' ar_misc_cash_distributions rows updated: ' ||
392 SQL%ROWCOUNT);
393 arp_standard.debug(' ARP_CORRECT_CCID.Correct_All_Invalid_CCID()-');
394 END IF;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 IF PG_DEBUG in ('Y', 'C') THEN
399 arp_standard.debug('EXCEPTION: ARP_CORRECT_CCID.Correct_All_Invalid_CCID()');
400 END IF;
401
402 END Correct_All_Invalid_CCID;
403
404
405 END ARP_CORRECT_CCID;
[Home] [Help]