[Home] [Help]
PACKAGE BODY: APPS.ARP_CTLGD_PKG
Source
1 PACKAGE BODY ARP_CTLGD_PKG AS
2 /* $Header: ARTILGDB.pls 120.21 2010/09/20 06:33:40 nemani ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /*--------------------------------------------------------+
6 | Dummy constants for use in update and lock operations |
7 +--------------------------------------------------------*/
8
9 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
10 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
11 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
12 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
13
14 pg_msg_level_debug binary_integer;
15
16 /*---------------------------------------------------------------+
17 | Package global variables to hold the parsed update cursors. |
18 | This allows the cursors to be reused without being reparsed. |
19 +---------------------------------------------------------------*/
20
21 pg_cursor1 integer := '';
22 pg_cursor2 integer := '';
23 pg_cursor3 integer := '';
24 pg_cursor4 integer := '';
25
26 /*-------------------------------------+
27 | WHO column values from FND_GLOBAL |
28 +-------------------------------------*/
29
30 pg_user_id number;
31 pg_conc_login_id number;
32 pg_login_id number;
33 pg_prog_appl_id number;
34 pg_conc_program_id number;
35
36
37 /*===========================================================================+
38 | PROCEDURE |
39 | display_dist_rec |
40 | |
41 | DESCRIPTION |
42 | Displays the values of all columns except creation_date and |
43 | last_update_date. |
44 | |
45 | SCOPE - PUBLIC |
46 | |
47 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
48 | arp_util.debug |
49 | |
50 | ARGUMENTS : IN: |
51 | p_dist_rec |
52 | OUT: |
53 | None |
54 | IN/ OUT: |
55 | None |
56 | |
57 | RETURNS : NONE |
58 | |
59 | NOTES |
60 | |
61 | MODIFICATION HISTORY |
62 | 19-JUL-95 Charlie Tomberg Created |
63 | |
64 +===========================================================================*/
65
66 PROCEDURE display_dist_rec( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype)
67 IS
68
69
70 BEGIN
71
72 arp_util.debug('arp_ctlgd_pkg.display_dist_rec()+');
73
74 arp_util.debug('************** Dump of ra_cust_trx_line_gl_dist record '||
75 '**************');
76
77 arp_util.debug('cust_trx_line_gl_dist_id: '
78 || p_dist_rec.cust_trx_line_gl_dist_id);
79 arp_util.debug('customer_trx_id: ' || p_dist_rec.customer_trx_id);
80 arp_util.debug('customer_trx_line_id: ' || p_dist_rec.customer_trx_line_id);
81 arp_util.debug('cust_trx_line_salesrep_id: '
82 || p_dist_rec.cust_trx_line_salesrep_id);
83 arp_util.debug('account_class: ' || p_dist_rec.account_class);
84 arp_util.debug('percent: ' || p_dist_rec.percent);
85 arp_util.debug('amount: ' || p_dist_rec.amount);
86 arp_util.debug('acctd_amount: ' || p_dist_rec.acctd_amount);
87 arp_util.debug('gl_date: ' || p_dist_rec.gl_date);
88 arp_util.debug('original_gl_date: ' || p_dist_rec.original_gl_date);
89 arp_util.debug('gl_posted_date: ' || p_dist_rec.gl_posted_date);
90 arp_util.debug('code_combination_id: ' || p_dist_rec.code_combination_id);
91 arp_util.debug('concatenated_segments: '
92 || p_dist_rec.concatenated_segments);
93 arp_util.debug('collected_tax_ccid: ' || p_dist_rec.collected_tax_ccid);
94 arp_util.debug('collected_tax_concat_seg: '
95 || p_dist_rec.collected_tax_concat_seg);
96 arp_util.debug('comments: ' || p_dist_rec.comments);
97 arp_util.debug('account_set_flag: ' || p_dist_rec.account_set_flag);
98 arp_util.debug('latest_rec_flag: ' || p_dist_rec.latest_rec_flag);
99 arp_util.debug('rec_offset_flag: ' || p_dist_rec.rec_offset_flag);
100 arp_util.debug('ussgl_transaction_code: '
101 || p_dist_rec.ussgl_transaction_code);
102 arp_util.debug('ussgl_transaction_code_context: '
103 || p_dist_rec.ussgl_transaction_code_context);
104 arp_util.debug('attribute_category: ' || p_dist_rec.attribute_category);
105 arp_util.debug('attribute1: ' || p_dist_rec.attribute1);
106 arp_util.debug('attribute2: ' || p_dist_rec.attribute2);
107 arp_util.debug('attribute3: ' || p_dist_rec.attribute3);
108 arp_util.debug('attribute4: ' || p_dist_rec.attribute4);
109 arp_util.debug('attribute5: ' || p_dist_rec.attribute5);
110 arp_util.debug('attribute6: ' || p_dist_rec.attribute6);
111 arp_util.debug('attribute7: ' || p_dist_rec.attribute7);
112 arp_util.debug('attribute8: ' || p_dist_rec.attribute8);
113 arp_util.debug('attribute9: ' || p_dist_rec.attribute9);
114 arp_util.debug('attribute10: ' || p_dist_rec.attribute10);
115 arp_util.debug('attribute11: ' || p_dist_rec.attribute11);
116 arp_util.debug('attribute12: ' || p_dist_rec.attribute12);
117 arp_util.debug('attribute13: ' || p_dist_rec.attribute13);
118 arp_util.debug('attribute14: ' || p_dist_rec.attribute14);
119 arp_util.debug('attribute15: ' || p_dist_rec.attribute15);
120 arp_util.debug('set_of_books_id: ' || p_dist_rec.set_of_books_id);
121 arp_util.debug('posting_control_id: ' || p_dist_rec.posting_control_id);
122 arp_util.debug('last_updated_by: ' || p_dist_rec.last_updated_by);
123 arp_util.debug('created_by: ' || p_dist_rec.created_by);
124 arp_util.debug('last_update_login: ' || p_dist_rec.last_update_login);
125 arp_util.debug('program_application_id: '
126 || p_dist_rec.program_application_id);
127 arp_util.debug('program_id: ' || p_dist_rec.program_id);
128 arp_util.debug('rounding_correction_flag:'|| p_dist_rec.rounding_correction_flag);
129 arp_util.debug('************** End ra_cust_trx_line_gl_dist record ' ||
130 '**************');
131
132 arp_util.debug('arp_ctlgd_pkg.display_dist_rec()-');
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.display_dist_rec()');
137 RAISE;
138
139 END;
140
141 /*===========================================================================+
142 | PROCEDURE |
143 | display_dist_p |
144 | |
145 | DESCRIPTION |
146 | Displays the values of all columns except creation_date and |
147 | last_update_date. |
148 | |
149 | SCOPE - PUBLIC |
150 | |
151 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
152 | arp_util.debug |
153 | |
154 | ARGUMENTS : IN: |
155 | p_cust_trx_line_gl_dist_id |
156 | OUT: |
157 | None |
158 | IN/ OUT: |
159 | None |
160 | |
161 | RETURNS : NONE |
162 | |
163 | NOTES |
164 | |
165 | MODIFICATION HISTORY |
166 | 13-JUL-95 Charlie Tomberg Created |
167 | |
168 +===========================================================================*/
169
170 PROCEDURE display_dist_p( p_cust_trx_line_gl_dist_id IN
171 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type)
172 IS
173
174 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
175
176 BEGIN
177
178 arp_util.debug('arp_ctlgd_pkg.display_dist_p()+');
179
180 arp_ctlgd_pkg.fetch_p(l_dist_rec, p_cust_trx_line_gl_dist_id);
181
182 arp_ctlgd_pkg.display_dist_rec(l_dist_rec);
183
184 arp_util.debug('arp_ctlgd_pkg.display_dist_p()-');
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.display_dist_p()');
189 RAISE;
190
191 END;
192
193
194 /*===========================================================================+
195 | PROCEDURE |
196 | display_dist_f_ctls_id |
197 | |
198 | DESCRIPTION |
199 | Displays the values of all columns except creation_date and |
200 | last_update_date. |
201 | |
202 | SCOPE - PUBLIC |
203 | |
204 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
205 | arp_util.debug |
206 | |
207 | ARGUMENTS : IN: |
208 | p_cust_trx_line_salesrep_id |
209 | OUT: |
210 | None |
211 | IN/ OUT: |
212 | None |
213 | |
214 | RETURNS : NONE |
215 | |
216 | NOTES |
217 | |
218 | MODIFICATION HISTORY |
219 | 13-JUL-95 Charlie Tomberg Created |
220 | |
221 +===========================================================================*/
222
223
224 PROCEDURE display_dist_f_ctls_id( p_cust_trx_line_salesrep_id IN
225 ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type)
226 IS
227
228 l_cust_trx_line_gl_dist_id
229 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
230
231 CURSOR srep_cursor IS
232 SELECT *
233 FROM ra_cust_trx_line_gl_dist
234 WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
235 ORDER BY cust_trx_line_gl_dist_id;
236
237
238 BEGIN
239
240 arp_util.debug('arp_ctlgd_pkg.display_dist_f_ctls_id()+');
241
242 arp_util.debug('=====================================================' ||
243 '==========================');
244 arp_util.debug('========== ' ||
245 ' Dump of ra_cust_trx_line_gl_dist records for ctls: '||
246 to_char( p_cust_trx_line_salesrep_id ) || ' ' ||
247 '==========');
248
249 FOR l_dist_rec IN srep_cursor LOOP
250 arp_ctlgd_pkg.display_dist_p(l_dist_rec.cust_trx_line_gl_dist_id);
251 END LOOP;
252
253 arp_util.debug('====== End ' ||
254 'Dump of ra_cust_trx_line_gl_dist records for ctls: '||
255 to_char( p_cust_trx_line_salesrep_id ) || ' ' ||
256 '=======');
257 arp_util.debug('=====================================================' ||
258 '==========================');
259
260 arp_util.debug('arp_ctlgd_pkg.display_dist_f_ctls_id()-');
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.display_dist_f_ctls_id()');
265 RAISE;
266
267 END;
268
269 /*===========================================================================+
270 | PROCEDURE |
271 | display_dist_f_ct_id |
272 | |
273 | DESCRIPTION |
274 | Displays the values of all columns except creation_date and |
275 | last_update_date. |
276 | |
277 | SCOPE - PUBLIC |
278 | |
279 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
280 | arp_util.debug |
281 | |
282 | ARGUMENTS : IN: |
283 | p_customer_trx_id |
284 | OUT: |
285 | None |
286 | IN/ OUT: |
287 | None |
288 | |
289 | RETURNS : NONE |
290 | |
291 | NOTES |
292 | |
293 | MODIFICATION HISTORY |
294 | 17-JUL-95 Charlie Tomberg Created |
295 | |
296 +===========================================================================*/
297
298
299 PROCEDURE display_dist_f_ct_id( p_customer_trx_id IN
300 ra_customer_trx.customer_trx_id%type)
301 IS
302
303 l_cust_trx_line_gl_dist_id
304 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
305
306 CURSOR srep_cursor IS
307 SELECT *
308 FROM ra_cust_trx_line_gl_dist
309 WHERE customer_trx_id = p_customer_trx_id
310 ORDER BY cust_trx_line_gl_dist_id;
311
312
313 BEGIN
314
315 arp_util.debug('arp_ctlgd_pkg.display_dist_f_ct_id()+');
316
317 arp_util.debug('=====================================================' ||
318 '==========================');
319 arp_util.debug('========== ' ||
320 ' Dump of ra_cust_trx_line_gl_dist records for ctid: '||
321 to_char( p_customer_trx_id ) || ' ' ||
322 '==========');
323
324 FOR l_dist_rec IN srep_cursor LOOP
325 arp_ctlgd_pkg.display_dist_p(l_dist_rec.cust_trx_line_gl_dist_id);
326 END LOOP;
327
328 arp_util.debug('====== End ' ||
329 'Dump of ra_cust_trx_line_gl_dist records for ctid: '||
330 to_char( p_customer_trx_id ) || ' ' ||
331 '=======');
332 arp_util.debug('=====================================================' ||
333 '==========================');
334
335 arp_util.debug('arp_ctlgd_pkg.display_dist_f_ct_id()-');
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.display_dist_f_ct_id()');
340 RAISE;
341
342 END;
343
344
345 /*===========================================================================+
346 | PROCEDURE |
347 | display_dist_f_ctl_id |
348 | |
349 | DESCRIPTION |
350 | Displays the values of all columns except creation_date and |
351 | last_update_date. |
352 | |
353 | SCOPE - PUBLIC |
354 | |
355 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
356 | arp_util.debug |
357 | |
358 | ARGUMENTS : IN: |
359 | p_customer_trx_line_id |
360 | OUT: |
361 | None |
362 | IN/ OUT: |
363 | None |
364 | |
365 | RETURNS : NONE |
366 | |
367 | NOTES |
368 | |
369 | MODIFICATION HISTORY |
370 | 04-AUG-95 Charlie Tomberg Created |
371 | |
372 +===========================================================================*/
373
374
375 PROCEDURE display_dist_f_ctl_id( p_customer_trx_line_id IN
376 ra_customer_trx_lines.customer_trx_line_id%type)
377 IS
378
379 l_cust_trx_line_gl_dist_id
380 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
381
382 CURSOR dist_cursor IS
383 SELECT *
384 FROM ra_cust_trx_line_gl_dist
385 WHERE customer_trx_line_id = p_customer_trx_line_id
386 ORDER BY cust_trx_line_gl_dist_id;
387
388
389 BEGIN
390
391 arp_util.debug('arp_ctlgd_pkg.display_dist_f_ctl_id()+');
392
393 arp_util.debug('=====================================================' ||
394 '==========================');
395 arp_util.debug('========== ' ||
396 ' Dump of ra_cust_trx_line_gl_dist records for ctlid: '||
397 to_char( p_customer_trx_line_id ) || ' ' ||
398 '==========');
399
400 FOR l_dist_rec IN dist_cursor LOOP
401 arp_ctlgd_pkg.display_dist_p(l_dist_rec.cust_trx_line_gl_dist_id);
402 END LOOP;
403
404 arp_util.debug('====== End ' ||
405 'Dump of ra_cust_trx_line_gl_dist records for ctlid: '||
406 to_char( p_customer_trx_line_id ) || ' ' ||
407 '=======');
408 arp_util.debug('=====================================================' ||
409 '==========================');
410
411 arp_util.debug('arp_ctlgd_pkg.display_dist_f_ctl_id()-');
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.display_dist_f_ctl_id()');
416 RAISE;
417
418 END;
419
420 /*===========================================================================+
421 | FUNCTION |
422 | get_number_dummy |
423 | |
424 | DESCRIPTION |
425 | This procedure returns the value of the AR_NUMBER DUMMY constant. |
426 | |
427 | SCOPE - PUBLIC |
428 | |
429 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
430 | arp_util.debug |
431 | |
432 | ARGUMENTS : IN: |
433 | None |
434 | OUT: |
435 | None |
436 | |
437 | RETURNS : value of AR_NUMBER_DUMMY |
438 | |
439 | NOTES |
440 | |
441 | MODIFICATION HISTORY |
442 | 19-JUN-95 Charlie Tomberg Created |
443 | |
444 +===========================================================================*/
445
446 FUNCTION get_number_dummy(p_null IN NUMBER DEFAULT null) RETURN number IS
447
448 BEGIN
449
450 arp_util.debug('arp_ctgd_pkg.get_number_dummy()+');
451
452 arp_util.debug('arp_ctgd_pkg.get_number_dummy()-');
453
454 return(AR_NUMBER_DUMMY);
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 arp_util.debug('EXCEPTION: arp_ctgd_pkg.get_number_dummy()');
459 RAISE;
460
461 END;
462
463
464 /*===========================================================================+
465 | PROCEDURE |
466 | merge_dist_recs |
467 | |
468 | DESCRIPTION |
469 | Merges the changed columns in p_new_dist_rec into the same columns |
470 | p_old_dist_rec and puts the result into p_out_dist_rec. Columns that |
471 | contain the dummy values are not changed. |
472 | |
473 | SCOPE - PUBLIC |
474 | |
475 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
476 | arp_util.debug |
477 | |
478 | ARGUMENTS : IN: |
479 | p_old_dist_rec |
480 | p_new_dist_rec |
481 | OUT: |
482 | None |
483 | IN/ OUT: |
484 | p_out_dist_rec |
485 | |
486 | RETURNS : NONE |
487 | |
488 | NOTES |
489 | |
490 | MODIFICATION HISTORY |
491 | 19-JUL-95 Charlie Tomberg Created |
492 | |
493 +===========================================================================*/
494
495 PROCEDURE merge_dist_recs(
496 p_old_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
497 p_new_dist_rec IN
498 ra_cust_trx_line_gl_dist%rowtype,
499 p_out_dist_rec IN OUT NOCOPY
500 ra_cust_trx_line_gl_dist%rowtype
501 ) IS
502
503 BEGIN
504
505 arp_util.debug('arp_ctls_pkg.merge_dist_recs()+');
506
507
508 IF ( p_new_dist_rec.cust_trx_line_gl_dist_id = AR_NUMBER_DUMMY )
509 THEN p_out_dist_rec.cust_trx_line_gl_dist_id :=
510 p_old_dist_rec.cust_trx_line_gl_dist_id;
511 ELSE p_out_dist_rec.cust_trx_line_gl_dist_id :=
512 p_new_dist_rec.cust_trx_line_gl_dist_id;
513 END IF;
514
515 IF ( p_new_dist_rec.customer_trx_id = AR_NUMBER_DUMMY )
516 THEN p_out_dist_rec.customer_trx_id := p_old_dist_rec.customer_trx_id;
517 ELSE p_out_dist_rec.customer_trx_id := p_new_dist_rec.customer_trx_id;
518 END IF;
519
520 IF ( p_new_dist_rec.customer_trx_line_id = AR_NUMBER_DUMMY )
521 THEN p_out_dist_rec.customer_trx_line_id :=
522 p_old_dist_rec.customer_trx_line_id;
523 ELSE p_out_dist_rec.customer_trx_line_id :=
524 p_new_dist_rec.customer_trx_line_id;
525 END IF;
526
527 IF ( p_new_dist_rec.cust_trx_line_salesrep_id = AR_NUMBER_DUMMY )
528 THEN p_out_dist_rec.cust_trx_line_salesrep_id :=
529 p_old_dist_rec.cust_trx_line_salesrep_id;
530 ELSE p_out_dist_rec.cust_trx_line_salesrep_id :=
531 p_new_dist_rec.cust_trx_line_salesrep_id;
532 END IF;
533
534 IF ( p_new_dist_rec.account_class = AR_TEXT_DUMMY )
535 THEN p_out_dist_rec.account_class := p_old_dist_rec.account_class;
536 ELSE p_out_dist_rec.account_class := p_new_dist_rec.account_class;
537 END IF;
538
539 IF ( p_new_dist_rec.percent = AR_NUMBER_DUMMY )
540 THEN p_out_dist_rec.percent := p_old_dist_rec.percent;
541 ELSE p_out_dist_rec.percent := p_new_dist_rec.percent;
542 END IF;
543
544 IF ( p_new_dist_rec.amount = AR_NUMBER_DUMMY )
545 THEN p_out_dist_rec.amount := p_old_dist_rec.amount;
546 ELSE p_out_dist_rec.amount := p_new_dist_rec.amount;
547 END IF;
548
549 IF ( p_new_dist_rec.acctd_amount = AR_NUMBER_DUMMY )
550 THEN p_out_dist_rec.acctd_amount := p_old_dist_rec.acctd_amount;
551 ELSE p_out_dist_rec.acctd_amount := p_new_dist_rec.acctd_amount;
552 END IF;
553
554 IF ( p_new_dist_rec.gl_date = AR_DATE_DUMMY )
555 THEN p_out_dist_rec.gl_date := p_old_dist_rec.gl_date;
556 ELSE p_out_dist_rec.gl_date := p_new_dist_rec.gl_date;
557 END IF;
558
559 IF ( p_new_dist_rec.original_gl_date = AR_DATE_DUMMY )
560 THEN p_out_dist_rec.original_gl_date := p_old_dist_rec.original_gl_date;
561 ELSE p_out_dist_rec.original_gl_date := p_new_dist_rec.original_gl_date;
562 END IF;
563
564 IF ( p_new_dist_rec.gl_posted_date = AR_DATE_DUMMY )
565 THEN p_out_dist_rec.gl_posted_date := p_old_dist_rec.gl_posted_date;
566 ELSE p_out_dist_rec.gl_posted_date := p_new_dist_rec.gl_posted_date;
567 END IF;
568
569 IF ( p_new_dist_rec.code_combination_id = AR_NUMBER_DUMMY )
570 THEN p_out_dist_rec.code_combination_id :=
571 p_old_dist_rec.code_combination_id;
572 ELSE p_out_dist_rec.code_combination_id :=
573 p_new_dist_rec.code_combination_id;
574 END IF;
575
576 IF ( p_new_dist_rec.concatenated_segments = AR_TEXT_DUMMY )
577 THEN p_out_dist_rec.concatenated_segments :=
578 p_old_dist_rec.concatenated_segments;
579 ELSE p_out_dist_rec.concatenated_segments :=
580 p_new_dist_rec.concatenated_segments;
581 END IF;
582
583 IF ( p_new_dist_rec.collected_tax_ccid = AR_NUMBER_DUMMY )
584 THEN p_out_dist_rec.collected_tax_ccid :=
585 p_old_dist_rec.collected_tax_ccid;
586 ELSE p_out_dist_rec.collected_tax_ccid :=
587 p_new_dist_rec.collected_tax_ccid;
588 END IF;
589
590 IF ( p_new_dist_rec.collected_tax_concat_seg = AR_TEXT_DUMMY )
591 THEN p_out_dist_rec.collected_tax_concat_seg :=
592 p_old_dist_rec.collected_tax_concat_seg;
593 ELSE p_out_dist_rec.collected_tax_concat_seg :=
594 p_new_dist_rec.collected_tax_concat_seg;
595 END IF;
596
597 IF ( p_new_dist_rec.comments = AR_TEXT_DUMMY )
598 THEN p_out_dist_rec.comments := p_old_dist_rec.comments;
599 ELSE p_out_dist_rec.comments := p_new_dist_rec.comments;
600 END IF;
601
602 IF ( p_new_dist_rec.account_set_flag = AR_FLAG_DUMMY )
603 THEN p_out_dist_rec.account_set_flag := p_old_dist_rec.account_set_flag;
604 ELSE p_out_dist_rec.account_set_flag := p_new_dist_rec.account_set_flag;
605 END IF;
606
607 IF ( p_new_dist_rec.latest_rec_flag = AR_FLAG_DUMMY )
608 THEN p_out_dist_rec.latest_rec_flag := p_old_dist_rec.latest_rec_flag;
609 ELSE p_out_dist_rec.latest_rec_flag := p_new_dist_rec.latest_rec_flag;
610 END IF;
611
612 /* bug 3598021 - 3630436 */
613 IF ( p_new_dist_rec.rec_offset_flag = AR_FLAG_DUMMY )
614 THEN p_out_dist_rec.rec_offset_flag := p_old_dist_rec.rec_offset_flag;
615 ELSE p_out_dist_rec.rec_offset_flag := p_new_dist_rec.rec_offset_flag;
616 END IF;
617
618 IF ( p_new_dist_rec.rounding_correction_flag = AR_FLAG_DUMMY )
619 THEN p_out_dist_rec.rounding_correction_flag := p_old_dist_rec.rounding_correction_flag;
620 ELSE p_out_dist_rec.rounding_correction_flag := p_new_dist_rec.rounding_correction_flag;
621 END IF;
622
623 IF ( p_new_dist_rec.ussgl_transaction_code = AR_TEXT_DUMMY )
624 THEN p_out_dist_rec.ussgl_transaction_code :=
625 p_old_dist_rec.ussgl_transaction_code;
626 ELSE p_out_dist_rec.ussgl_transaction_code :=
627 p_new_dist_rec.ussgl_transaction_code;
628 END IF;
629
630 IF ( p_new_dist_rec.ussgl_transaction_code_context = AR_TEXT_DUMMY )
631 THEN p_out_dist_rec.ussgl_transaction_code_context :=
632 p_old_dist_rec.ussgl_transaction_code_context;
633 ELSE p_out_dist_rec.ussgl_transaction_code_context :=
634 p_new_dist_rec.ussgl_transaction_code_context;
635 END IF;
636
637 IF ( p_new_dist_rec.attribute_category = AR_TEXT_DUMMY )
638 THEN p_out_dist_rec.attribute_category :=
639 p_old_dist_rec.attribute_category;
640 ELSE p_out_dist_rec.attribute_category :=
641 p_new_dist_rec.attribute_category;
642 END IF;
643
644 IF ( p_new_dist_rec.attribute1 = AR_TEXT_DUMMY )
645 THEN p_out_dist_rec.attribute1 := p_old_dist_rec.attribute1;
646 ELSE p_out_dist_rec.attribute1 := p_new_dist_rec.attribute1;
647 END IF;
648
649 IF ( p_new_dist_rec.attribute2 = AR_TEXT_DUMMY )
650 THEN p_out_dist_rec.attribute2 := p_old_dist_rec.attribute2;
651 ELSE p_out_dist_rec.attribute2 := p_new_dist_rec.attribute2;
652 END IF;
653
654 IF ( p_new_dist_rec.attribute3 = AR_TEXT_DUMMY )
655 THEN p_out_dist_rec.attribute3 := p_old_dist_rec.attribute3;
656 ELSE p_out_dist_rec.attribute3 := p_new_dist_rec.attribute3;
657 END IF;
658
659 IF ( p_new_dist_rec.attribute4 = AR_TEXT_DUMMY )
660 THEN p_out_dist_rec.attribute4 := p_old_dist_rec.attribute4;
661 ELSE p_out_dist_rec.attribute4 := p_new_dist_rec.attribute4;
662 END IF;
663
664 IF ( p_new_dist_rec.attribute5 = AR_TEXT_DUMMY )
665 THEN p_out_dist_rec.attribute5 := p_old_dist_rec.attribute5;
666 ELSE p_out_dist_rec.attribute5 := p_new_dist_rec.attribute5;
667 END IF;
668
669 IF ( p_new_dist_rec.attribute6 = AR_TEXT_DUMMY )
670 THEN p_out_dist_rec.attribute6 := p_old_dist_rec.attribute6;
671 ELSE p_out_dist_rec.attribute6 := p_new_dist_rec.attribute6;
672 END IF;
673
674 IF ( p_new_dist_rec.attribute7 = AR_TEXT_DUMMY )
675 THEN p_out_dist_rec.attribute7 := p_old_dist_rec.attribute7;
676 ELSE p_out_dist_rec.attribute7 := p_new_dist_rec.attribute7;
677 END IF;
678
679 IF ( p_new_dist_rec.attribute8 = AR_TEXT_DUMMY )
680 THEN p_out_dist_rec.attribute8 := p_old_dist_rec.attribute8;
681 ELSE p_out_dist_rec.attribute8 := p_new_dist_rec.attribute8;
682 END IF;
683
684 IF ( p_new_dist_rec.attribute9 = AR_TEXT_DUMMY )
685 THEN p_out_dist_rec.attribute9 := p_old_dist_rec.attribute9;
686 ELSE p_out_dist_rec.attribute9 := p_new_dist_rec.attribute9;
687 END IF;
688
689 IF ( p_new_dist_rec.attribute10 = AR_TEXT_DUMMY )
690 THEN p_out_dist_rec.attribute10 := p_old_dist_rec.attribute10;
691 ELSE p_out_dist_rec.attribute10 := p_new_dist_rec.attribute10;
692 END IF;
693
694 IF ( p_new_dist_rec.attribute11 = AR_TEXT_DUMMY )
695 THEN p_out_dist_rec.attribute11 := p_old_dist_rec.attribute11;
696 ELSE p_out_dist_rec.attribute11 := p_new_dist_rec.attribute11;
697 END IF;
698
699 IF ( p_new_dist_rec.attribute12 = AR_TEXT_DUMMY )
700 THEN p_out_dist_rec.attribute12 := p_old_dist_rec.attribute12;
701 ELSE p_out_dist_rec.attribute12 := p_new_dist_rec.attribute12;
702 END IF;
703
704 IF ( p_new_dist_rec.attribute13 = AR_TEXT_DUMMY )
705 THEN p_out_dist_rec.attribute13 := p_old_dist_rec.attribute13;
706 ELSE p_out_dist_rec.attribute13 := p_new_dist_rec.attribute13;
707 END IF;
708
709 IF ( p_new_dist_rec.attribute14 = AR_TEXT_DUMMY )
710 THEN p_out_dist_rec.attribute14 := p_old_dist_rec.attribute14;
711 ELSE p_out_dist_rec.attribute14 := p_new_dist_rec.attribute14;
712 END IF;
713
714 IF ( p_new_dist_rec.attribute15 = AR_TEXT_DUMMY )
715 THEN p_out_dist_rec.attribute15 := p_old_dist_rec.attribute15;
716 ELSE p_out_dist_rec.attribute15 := p_new_dist_rec.attribute15;
717 END IF;
718
719 IF ( p_new_dist_rec.set_of_books_id = AR_NUMBER_DUMMY )
720 THEN p_out_dist_rec.set_of_books_id := p_old_dist_rec.set_of_books_id;
721 ELSE p_out_dist_rec.set_of_books_id := p_new_dist_rec.set_of_books_id;
722 END IF;
723
724 IF ( p_new_dist_rec.posting_control_id = AR_NUMBER_DUMMY )
725 THEN p_out_dist_rec.posting_control_id :=
726 p_old_dist_rec.posting_control_id;
727 ELSE p_out_dist_rec.posting_control_id :=
728 p_new_dist_rec.posting_control_id;
729 END IF;
730
731 IF ( p_new_dist_rec.last_update_date = AR_DATE_DUMMY )
732 THEN p_out_dist_rec.last_update_date := p_old_dist_rec.last_update_date;
733 ELSE p_out_dist_rec.last_update_date := p_new_dist_rec.last_update_date;
734 END IF;
735
736 IF ( p_new_dist_rec.last_updated_by = AR_NUMBER_DUMMY )
737 THEN p_out_dist_rec.last_updated_by := p_old_dist_rec.last_updated_by;
738 ELSE p_out_dist_rec.last_updated_by := p_new_dist_rec.last_updated_by;
739 END IF;
740
741 IF ( p_new_dist_rec.creation_date = AR_DATE_DUMMY )
742 THEN p_out_dist_rec.creation_date := p_old_dist_rec.creation_date;
743 ELSE p_out_dist_rec.creation_date := p_new_dist_rec.creation_date;
744 END IF;
745
746 IF ( p_new_dist_rec.created_by = AR_NUMBER_DUMMY )
747 THEN p_out_dist_rec.created_by := p_old_dist_rec.created_by;
748 ELSE p_out_dist_rec.created_by := p_new_dist_rec.created_by;
749 END IF;
750
751 IF ( p_new_dist_rec.last_update_login = AR_NUMBER_DUMMY )
752 THEN p_out_dist_rec.last_update_login :=
753 p_old_dist_rec.last_update_login;
754 ELSE p_out_dist_rec.last_update_login :=
755 p_new_dist_rec.last_update_login;
756 END IF;
757
758 IF ( p_new_dist_rec.program_application_id = AR_NUMBER_DUMMY )
759 THEN p_out_dist_rec.program_application_id :=
760 p_old_dist_rec.program_application_id;
761 ELSE p_out_dist_rec.program_application_id :=
762 p_new_dist_rec.program_application_id;
763 END IF;
764
765 IF ( p_new_dist_rec.program_id = AR_NUMBER_DUMMY )
766 THEN p_out_dist_rec.program_id := p_old_dist_rec.program_id;
767 ELSE p_out_dist_rec.program_id := p_new_dist_rec.program_id;
768 END IF;
769
770 IF ( p_new_dist_rec.program_update_date = AR_DATE_DUMMY )
771 THEN p_out_dist_rec.program_update_date :=
772 p_old_dist_rec.program_update_date;
773 ELSE p_out_dist_rec.program_update_date :=
774 p_new_dist_rec.program_update_date;
775 END IF;
776
777
778 arp_util.debug('arp_ctls_pkg.merge_dist_recs()-');
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 arp_util.debug('EXCEPTION: merge_dist_recs.merge_dist_recs()');
783 RAISE;
784
785 END;
786
787
788 /*===========================================================================+
789 | PROCEDURE |
790 | bind_dist_variables |
791 | |
792 | DESCRIPTION |
793 | Binds variables from the record variable to the bind variables |
794 | in the dynamic SQL update statement. |
795 | |
796 | SCOPE - PRIVATE |
797 | |
798 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
799 | dbms_sql.bind_variable |
800 | arp_util.debug |
801 | |
802 | ARGUMENTS : IN: |
803 | p_update_cursor - ID of the update cursor |
804 | p_dist_rec - ra_cust_trx_line_gl_dist record |
805 | p_exchange_rate |
806 | p_currency_code |
807 | p_precision |
808 | p_mau |
809 | OUT: |
810 | None |
811 | |
812 | RETURNS : NONE |
813 | |
814 | NOTES |
815 | |
816 | MODIFICATION HISTORY |
817 | 09-JUN-95 Charlie Tomberg Created |
818 | |
819 +===========================================================================*/
820
821
822 PROCEDURE bind_dist_variables(
823 p_update_cursor IN integer,
824 p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
825 p_exchange_rate IN ra_customer_trx.exchange_rate%type,
826 p_currency_code IN fnd_currencies.currency_code%type,
827 p_precision IN fnd_currencies.precision%type,
828 p_mau IN
829 fnd_currencies.minimum_accountable_unit%type)
830 IS
831
832 BEGIN
833
834 arp_util.debug('arp_ctlgd_pkg.bind_dist_variables()+');
835
836 /*------------------+
837 | Dummy constants |
838 +------------------*/
839
840 dbms_sql.bind_variable(p_update_cursor, ':ar_text_dummy',
841 AR_TEXT_DUMMY);
842
843 dbms_sql.bind_variable(p_update_cursor, ':ar_flag_dummy',
844 AR_FLAG_DUMMY);
845
846 dbms_sql.bind_variable(p_update_cursor, ':ar_number_dummy',
847 AR_NUMBER_DUMMY);
848
849 dbms_sql.bind_variable(p_update_cursor, ':ar_date_dummy',
850 AR_DATE_DUMMY);
851
852 /*------------------+
853 | WHO variables |
854 +------------------*/
855
856 dbms_sql.bind_variable(p_update_cursor, ':pg_user_id',
857 pg_user_id);
858
859 dbms_sql.bind_variable(p_update_cursor, ':pg_login_id',
860 pg_login_id);
861
862 dbms_sql.bind_variable(p_update_cursor, ':pg_conc_login_id',
863 pg_conc_login_id);
864
865 /*----------------------------------------+
866 | Rounding and exchange rate variables |
867 +----------------------------------------*/
868
869 dbms_sql.bind_variable(p_update_cursor, ':exchange_rate',
870 p_exchange_rate);
871
872 dbms_sql.bind_variable(p_update_cursor, ':currency_code',
873 p_currency_code);
874
875 dbms_sql.bind_variable(p_update_cursor, ':precision',
876 p_precision);
877
878 dbms_sql.bind_variable(p_update_cursor, ':mau',
879 p_mau);
880
881 /*----------------------------------------------+
882 | Bind variables for all columns in the table |
883 +----------------------------------------------*/
884
885 dbms_sql.bind_variable(p_update_cursor, ':cust_trx_line_salesrep_id',
886 p_dist_rec.cust_trx_line_salesrep_id);
887
888 dbms_sql.bind_variable(p_update_cursor, ':cust_trx_line_gl_dist_id',
889 p_dist_rec.cust_trx_line_gl_dist_id);
890
891 dbms_sql.bind_variable(p_update_cursor, ':customer_trx_id',
892 p_dist_rec.customer_trx_id);
893
894 dbms_sql.bind_variable(p_update_cursor, ':customer_trx_line_id',
895 p_dist_rec.customer_trx_line_id);
896
897 dbms_sql.bind_variable(p_update_cursor, ':cust_trx_line_salesrep_id',
898 p_dist_rec.cust_trx_line_salesrep_id);
899
900 dbms_sql.bind_variable(p_update_cursor, ':account_class',
901 p_dist_rec.account_class);
902
903 dbms_sql.bind_variable(p_update_cursor, ':percent',
904 p_dist_rec.percent);
905
906 dbms_sql.bind_variable(p_update_cursor, ':amount',
907 p_dist_rec.amount);
908
909 dbms_sql.bind_variable(p_update_cursor, ':acctd_amount',
910 p_dist_rec.acctd_amount);
911
912 dbms_sql.bind_variable(p_update_cursor, ':gl_date',
913 p_dist_rec.gl_date);
914
915 dbms_sql.bind_variable(p_update_cursor, ':original_gl_date',
916 p_dist_rec.original_gl_date);
917
918 dbms_sql.bind_variable(p_update_cursor, ':gl_posted_date',
919 p_dist_rec.gl_posted_date);
920
921 dbms_sql.bind_variable(p_update_cursor, ':code_combination_id',
922 p_dist_rec.code_combination_id);
923
924 dbms_sql.bind_variable(p_update_cursor, ':concatenated_segments',
925 p_dist_rec.concatenated_segments);
926
927 dbms_sql.bind_variable(p_update_cursor, ':collected_tax_ccid',
928 p_dist_rec.collected_tax_ccid);
929
930 dbms_sql.bind_variable(p_update_cursor, ':collected_tax_concat_seg',
931 p_dist_rec.collected_tax_concat_seg);
932
933 dbms_sql.bind_variable(p_update_cursor, ':comments',
934 p_dist_rec.comments);
935
936 dbms_sql.bind_variable(p_update_cursor, ':account_set_flag',
937 p_dist_rec.account_set_flag);
938
939 dbms_sql.bind_variable(p_update_cursor, ':latest_rec_flag',
940 p_dist_rec.latest_rec_flag);
941
942 /* bug 3598021 - 3630436 */
943 dbms_sql.bind_variable(p_update_cursor, ':rec_offset_flag',
944 p_dist_rec.rec_offset_flag);
945
946 dbms_sql.bind_variable(p_update_cursor, ':rounding_correction_flag',
947 p_dist_rec.rounding_correction_flag);
948
949 dbms_sql.bind_variable(p_update_cursor, ':ussgl_transaction_code',
950 p_dist_rec.ussgl_transaction_code);
951
952 dbms_sql.bind_variable(p_update_cursor, ':ussgl_transaction_code_context',
953 p_dist_rec.ussgl_transaction_code_context);
954
955 dbms_sql.bind_variable(p_update_cursor, ':attribute_category',
956 p_dist_rec.attribute_category);
957
958 dbms_sql.bind_variable(p_update_cursor, ':attribute1',
959 p_dist_rec.attribute1);
960
961 dbms_sql.bind_variable(p_update_cursor, ':attribute2',
962 p_dist_rec.attribute2);
963
964 dbms_sql.bind_variable(p_update_cursor, ':attribute3',
965 p_dist_rec.attribute3);
966
967 dbms_sql.bind_variable(p_update_cursor, ':attribute4',
968 p_dist_rec.attribute4);
969
970 dbms_sql.bind_variable(p_update_cursor, ':attribute5',
971 p_dist_rec.attribute5);
972
973 dbms_sql.bind_variable(p_update_cursor, ':attribute6',
974 p_dist_rec.attribute6);
975
976 dbms_sql.bind_variable(p_update_cursor, ':attribute7',
977 p_dist_rec.attribute7);
978
979 dbms_sql.bind_variable(p_update_cursor, ':attribute8',
980 p_dist_rec.attribute8);
981
982 dbms_sql.bind_variable(p_update_cursor, ':attribute9',
983 p_dist_rec.attribute9);
984
985 dbms_sql.bind_variable(p_update_cursor, ':attribute10',
986 p_dist_rec.attribute10);
987
988 dbms_sql.bind_variable(p_update_cursor, ':attribute11',
989 p_dist_rec.attribute11);
990
991 dbms_sql.bind_variable(p_update_cursor, ':attribute12',
992 p_dist_rec.attribute12);
993
994 dbms_sql.bind_variable(p_update_cursor, ':attribute13',
995 p_dist_rec.attribute13);
996
997 dbms_sql.bind_variable(p_update_cursor, ':attribute14',
998 p_dist_rec.attribute14);
999
1000 dbms_sql.bind_variable(p_update_cursor, ':attribute15',
1001 p_dist_rec.attribute15);
1002
1003 dbms_sql.bind_variable(p_update_cursor, ':set_of_books_id',
1004 p_dist_rec.set_of_books_id);
1005
1006 dbms_sql.bind_variable(p_update_cursor, ':posting_control_id',
1007 p_dist_rec.posting_control_id);
1008
1009 dbms_sql.bind_variable(p_update_cursor, ':last_update_date',
1010 p_dist_rec.last_update_date);
1011
1012 dbms_sql.bind_variable(p_update_cursor, ':last_updated_by',
1013 p_dist_rec.last_updated_by);
1014
1015 dbms_sql.bind_variable(p_update_cursor, ':creation_date',
1016 p_dist_rec.creation_date);
1017
1018 dbms_sql.bind_variable(p_update_cursor, ':created_by',
1019 p_dist_rec.created_by);
1020
1021 dbms_sql.bind_variable(p_update_cursor, ':last_update_login',
1022 p_dist_rec.last_update_login);
1023
1024 dbms_sql.bind_variable(p_update_cursor, ':program_application_id',
1025 p_dist_rec.program_application_id);
1026
1027 dbms_sql.bind_variable(p_update_cursor, ':program_id',
1028 p_dist_rec.program_id);
1029
1030 dbms_sql.bind_variable(p_update_cursor, ':program_update_date',
1031 p_dist_rec.program_update_date);
1032 dbms_sql.bind_variable(p_update_cursor, ':ccid_change_flag',
1033 p_dist_rec.ccid_change_flag); /* Bug 8788491 */
1034
1035 arp_util.debug('arp_ctlgd_pkg.bind_dist_variables()-');
1036
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.bind_dist_variables()');
1040 RAISE;
1041
1042 END;
1043
1044
1045 /*===========================================================================+
1046 | PROCEDURE |
1047 | construct_dist_update_stmt |
1048 | |
1049 | DESCRIPTION |
1050 | Copies the text of the dynamic SQL update statement into the |
1051 | out NOCOPY paramater. The update statement does not contain a where clause |
1052 | since this is the dynamic part that is added later. |
1053 | |
1054 | SCOPE - PRIVATE |
1055 | |
1056 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1057 | arpcurr.functional_amount |
1058 | arp_util.debug |
1059 | |
1060 | ARGUMENTS : IN: |
1061 | None. |
1062 | OUT: |
1063 | update_text - text of the update statement |
1064 | |
1065 | RETURNS : NONE |
1066 | |
1067 | NOTES |
1068 | This statement only updates columns in the dist record that do not |
1069 | contain the dummy values that indicate that they should not be changed.|
1070 | |
1071 | MODIFICATION HISTORY |
1072 | 09-JUN-95 Charlie Tomberg Created |
1073 | |
1074 +===========================================================================*/
1075
1076 PROCEDURE construct_dist_update_stmt( update_text OUT NOCOPY varchar2) IS
1077
1078 BEGIN
1079 arp_util.debug('arp_ctlgd_pkg.construct_dist_update_stmt()+');
1080
1081 update_text :=
1082 'UPDATE ra_cust_trx_line_gl_dist
1083 SET cust_trx_line_gl_dist_id =
1084 DECODE(:cust_trx_line_gl_dist_id,
1085 :ar_number_dummy, cust_trx_line_gl_dist_id,
1086 :cust_trx_line_gl_dist_id),
1087 customer_trx_id =
1088 DECODE(:customer_trx_id,
1089 :ar_number_dummy, customer_trx_id,
1090 :customer_trx_id),
1091 customer_trx_line_id =
1092 DECODE(:customer_trx_line_id,
1093 :ar_number_dummy, customer_trx_line_id,
1094 :customer_trx_line_id),
1095 cust_trx_line_salesrep_id =
1096 DECODE(:cust_trx_line_salesrep_id,
1097 :ar_number_dummy, cust_trx_line_salesrep_id,
1098 :cust_trx_line_salesrep_id),
1099 account_class =
1100 DECODE(:account_class,
1101 :ar_text_dummy, account_class,
1102 :account_class),
1103 percent =
1104 DECODE(:percent,
1105 :ar_number_dummy, percent,
1106 :percent),
1107 amount =
1108 DECODE(:amount,
1109 :ar_number_dummy, amount,
1110 :amount),
1111 /* calculate the accounted amount only if
1112 - a new accounted amount has not ben passed in AND
1113 - the amount has changed. */
1114 acctd_amount =
1115 DECODE(:acctd_amount,
1116 :ar_number_dummy, decode(:amount,
1117 amount, acctd_amount,
1118 :ar_number_dummy, acctd_amount,
1119 null, null,
1120 arpcurr.functional_amount(
1121 :amount,
1122 :currency_code,
1123 :exchange_rate,
1124 :precision,
1125 :mau)
1126 ),
1127 :acctd_amount),
1128 gl_date =
1129 -- Only uodate the GL Date for Account Set = N
1130 -- and Receivable records.
1131 DECODE(
1132 DECODE(:account_set_flag,
1133 :ar_flag_dummy, account_set_flag,
1134 :account_set_flag) ||
1135 DECODE(:account_class,
1136 :ar_text_dummy, account_class,
1137 :account_class),
1138 ''YCHARGES'', TO_DATE(NULL),
1139 ''YFREIGHT'', TO_DATE(NULL),
1140 ''YREV'', TO_DATE(NULL),
1141 ''YSUSPENSE'', TO_DATE(NULL),
1142 ''YTAX'', TO_DATE(NULL),
1143 ''YUNBILL'', TO_DATE(NULL),
1144 ''YUNEARN'', TO_DATE(NULL),
1145 DECODE(:gl_date,
1146 :ar_date_dummy, gl_date,
1147 :gl_date)
1148 ),
1149 original_gl_date =
1150 -- Only uodate the GL Date for Account Set = N
1151 -- and Receivable records.
1152 DECODE(
1153 DECODE(:account_set_flag,
1154 :ar_flag_dummy, account_set_flag,
1155 :account_set_flag) ||
1156 DECODE(:account_class,
1157 :ar_text_dummy, account_class,
1158 :account_class),
1159 ''YCHARGES'', TO_DATE(NULL),
1160 ''YFREIGHT'', TO_DATE(NULL),
1161 ''YREV'', TO_DATE(NULL),
1162 ''YSUSPENSE'', TO_DATE(NULL),
1163 ''YTAX'', TO_DATE(NULL),
1164 ''YUNBILL'', TO_DATE(NULL),
1165 ''YUNEARN'', TO_DATE(NULL),
1166 DECODE(:original_gl_date,
1167 :ar_date_dummy,
1168 original_gl_date,
1169 :original_gl_date)
1170 ),
1171 gl_posted_date =
1172 DECODE(:gl_posted_date,
1173 :ar_date_dummy, gl_posted_date,
1174 :gl_posted_date),
1175 code_combination_id =
1176 DECODE(:code_combination_id,
1177 :ar_number_dummy, code_combination_id,
1178 :code_combination_id),
1179 concatenated_segments =
1180 DECODE(:concatenated_segments,
1181 :ar_text_dummy, concatenated_segments,
1182 :concatenated_segments),
1183 collected_tax_ccid =
1184 DECODE(:collected_tax_ccid,
1185 :ar_number_dummy, collected_tax_ccid,
1186 :collected_tax_ccid),
1187 collected_tax_concat_seg =
1188 DECODE(:collected_tax_concat_seg,
1189 :ar_text_dummy, collected_tax_concat_seg,
1190 :collected_tax_concat_seg),
1191 comments =
1192 DECODE(:comments,
1193 :ar_text_dummy, comments,
1194 :comments),
1195 account_set_flag =
1196 DECODE(:account_set_flag,
1197 :ar_flag_dummy, account_set_flag,
1198 :account_set_flag),
1199 latest_rec_flag =
1200 DECODE(:latest_rec_flag,
1201 :ar_flag_dummy, latest_rec_flag,
1202 :latest_rec_flag),
1203 rec_offset_flag =
1204 DECODE(:rec_offset_flag,
1205 :ar_flag_dummy, rec_offset_flag,
1206 :rec_offset_flag),
1207 rounding_correction_flag =
1208 DECODE(:rounding_correction_flag,
1209 :ar_flag_dummy, rounding_correction_flag,
1210 :rounding_correction_flag),
1211 ussgl_transaction_code =
1212 DECODE(:ussgl_transaction_code,
1213 :ar_text_dummy, ussgl_transaction_code,
1214 :ussgl_transaction_code),
1215 ussgl_transaction_code_context =
1216 DECODE(:ussgl_transaction_code_context,
1217 :ar_text_dummy, ussgl_transaction_code_context,
1218 :ussgl_transaction_code_context),
1219 attribute_category =
1220 DECODE(:attribute_category,
1221 :ar_text_dummy, attribute_category,
1222 :attribute_category),
1223 attribute1 =
1224 DECODE(:attribute1,
1225 :ar_text_dummy, attribute1,
1226 :attribute1),
1227 attribute2 =
1228 DECODE(:attribute2,
1229 :ar_text_dummy, attribute2,
1230 :attribute2),
1231 attribute3 =
1232 DECODE(:attribute3,
1233 :ar_text_dummy, attribute3,
1234 :attribute3),
1235 attribute4 =
1236 DECODE(:attribute4,
1237 :ar_text_dummy, attribute4,
1238 :attribute4),
1239 attribute5 =
1240 DECODE(:attribute5,
1241 :ar_text_dummy, attribute5,
1242 :attribute5),
1243 attribute6 =
1244 DECODE(:attribute6,
1245 :ar_text_dummy, attribute6,
1246 :attribute6),
1247 attribute7 =
1248 DECODE(:attribute7,
1249 :ar_text_dummy, attribute7,
1250 :attribute7),
1251 attribute8 =
1252 DECODE(:attribute8,
1253 :ar_text_dummy, attribute8,
1254 :attribute8),
1255 attribute9 =
1256 DECODE(:attribute9,
1257 :ar_text_dummy, attribute9,
1258 :attribute9),
1259 attribute10 =
1260 DECODE(:attribute10,
1261 :ar_text_dummy, attribute10,
1262 :attribute10),
1263 attribute11 =
1264 DECODE(:attribute11,
1265 :ar_text_dummy, attribute11,
1266 :attribute11),
1267 attribute12 =
1268 DECODE(:attribute12,
1269 :ar_text_dummy, attribute12,
1270 :attribute12),
1271 attribute13 =
1272 DECODE(:attribute13,
1273 :ar_text_dummy, attribute13,
1274 :attribute13),
1275 attribute14 =
1276 DECODE(:attribute14,
1277 :ar_text_dummy, attribute14,
1278 :attribute14),
1279 attribute15 =
1280 DECODE(:attribute15,
1281 :ar_text_dummy, attribute15,
1282 :attribute15),
1283 set_of_books_id =
1284 DECODE(:set_of_books_id,
1285 :ar_number_dummy, set_of_books_id,
1286 :set_of_books_id),
1287 posting_control_id =
1288 DECODE(:posting_control_id,
1289 :ar_number_dummy, posting_control_id,
1290 :posting_control_id),
1291 last_update_date =
1292 DECODE(:last_update_date,
1293 :ar_date_dummy, sysdate,
1294 :last_update_date),
1295 last_updated_by =
1296 DECODE(:last_updated_by,
1297 :ar_number_dummy, :pg_user_id,
1298 :last_updated_by),
1299 creation_date =
1300 DECODE(:creation_date,
1301 :ar_date_dummy, creation_date,
1302 :creation_date),
1303 created_by =
1304 DECODE(:created_by,
1305 :ar_number_dummy, created_by,
1306 :created_by),
1307 last_update_login =
1308 DECODE(:last_update_login,
1309 :ar_number_dummy, nvl(:pg_conc_login_id,
1310 :pg_login_id),
1311 :last_update_login),
1312 program_application_id =
1313 DECODE(:program_application_id,
1314 :ar_number_dummy, program_application_id,
1315 :program_application_id),
1316 program_id =
1317 DECODE(:program_id,
1318 :ar_number_dummy, program_id,
1319 :program_id),
1320 program_update_date =
1321 DECODE(:program_update_date,
1322 :ar_date_dummy, program_update_date,
1323 :program_update_date),
1324 ccid_change_flag =
1325 DECODE(:ccid_change_flag,
1326 :ar_flag_dummy, ccid_change_flag,
1327 :ccid_change_flag)'; /* Bug 8788491 */
1328
1329
1330 arp_util.debug('arp_ctlgd_pkg.construct_dist_update_stmt()-');
1331
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.construct_dist_update_stmt()');
1335 RAISE;
1336
1337 END;
1338
1339 /*===========================================================================+
1340 | PROCEDURE |
1341 | generic_update |
1342 | |
1343 | DESCRIPTION |
1344 | This procedure Updates records in ra_cust_trx_line_gl_dist |
1345 | identified by the where clause that is passed in as a parameter. Only |
1346 | those columns in the dist record parameter that do not contain the |
1347 | special dummy values are updated. |
1348 | |
1349 | SCOPE - PRIVATE |
1350 | |
1351 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1352 | arp_util.debug |
1353 | dbms_sql.open_cursor |
1354 | dbms_sql.parse |
1355 | dbms_sql.execute |
1356 | dbms_sql.close_cursor |
1357 | |
1358 | ARGUMENTS : IN: |
1359 | p_update_cursor - identifies the cursor to use |
1360 | p_where_clause - identifies which rows to update |
1361 | p_where1 - value to bind into where clause |
1362 | p_account_set_flag - value is used to restrict update |
1363 | p_account_class - value is used to restrict update |
1364 | p_exchange_rate |
1365 | p_currency_code |
1366 | p_precision |
1367 | p_mau |
1368 | p_dist_rec - contains the new dist values |
1369 | OUT: |
1370 | None |
1371 | |
1372 | RETURNS : NONE |
1373 | |
1374 | NOTES |
1375 | |
1376 | MODIFICATION HISTORY |
1377 | 09-JUN-95 Charlie Tomberg Created |
1378 | |
1379 +===========================================================================*/
1380
1381 PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
1382 p_where_clause IN varchar2,
1383 p_where1 IN number,
1384 p_account_set_flag IN
1385 ra_cust_trx_line_gl_dist.account_set_flag%type,
1386 p_account_class IN
1387 ra_cust_trx_line_gl_dist.account_class%type,
1388 p_exchange_rate IN ra_customer_trx.exchange_rate%type,
1389 p_currency_code IN fnd_currencies.currency_code%type,
1390 p_precision IN fnd_currencies.precision%type,
1391 p_mau IN
1392 fnd_currencies.minimum_accountable_unit%type,
1393 p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype)
1394 IS
1395
1396 l_count number;
1397 l_update_statement varchar2(25000);
1398 gl_dist_array dbms_sql.number_table; /* mrc */
1399
1400 BEGIN
1401 arp_util.debug('arp_ctlgd_pkg.generic_update()+');
1402
1403 /*--------------------------------------------------------------+
1404 | If this update statement has not already been parsed, |
1405 | construct the statement and parse it. |
1406 | Otherwise, use the already parsed statement and rebind its |
1407 | variables. |
1408 +--------------------------------------------------------------*/
1409
1410 IF (p_update_cursor IS NULL)
1411 THEN
1412 p_update_cursor := dbms_sql.open_cursor;
1413
1414 /*---------------------------------+
1415 | Construct the update statement |
1416 +---------------------------------*/
1417
1418 arp_ctlgd_pkg.construct_dist_update_stmt(l_update_statement);
1419
1420 l_update_statement := l_update_statement || p_where_clause;
1421
1422 /*--------------------------------------------------------+
1423 | added on variables for bulk collect for mrc processing |
1424 +--------------------------------------------------------*/
1425
1426 l_update_statement := l_update_statement ||
1427 ' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
1428
1429 /*-----------------------------------------------+
1430 | Parse, bind, execute and close the statement |
1431 +-----------------------------------------------*/
1432
1433 dbms_sql.parse(p_update_cursor,
1434 l_update_statement,
1435 dbms_sql.v7);
1436
1437 END IF;
1438
1439 arp_ctlgd_pkg.bind_dist_variables(p_update_cursor,
1440 p_dist_rec,
1441 p_exchange_rate,
1442 p_currency_code,
1443 p_precision,
1444 p_mau);
1445
1446 /*-----------------------------------------+
1447 | Bind the variables in the where clause |
1448 +-----------------------------------------*/
1449
1450 dbms_sql.bind_variable(p_update_cursor, ':where_1',
1451 p_where1);
1452
1453 dbms_sql.bind_variable(p_update_cursor, ':where_account_set_flag',
1454 p_account_set_flag);
1455
1456 dbms_sql.bind_variable(p_update_cursor, ':where_account_class',
1457 p_account_class);
1458
1459 /*-----------------------+
1460 | bind output variable |
1461 +-----------------------*/
1462 dbms_sql.bind_array(p_update_cursor,':gl_dist_key_value',
1463 gl_dist_array);
1464
1465 l_count := dbms_sql.execute(p_update_cursor);
1466
1467 arp_util.debug( to_char(l_count) || ' rows updated');
1468
1469 /*------------------------------------------+
1470 | get RETURNING COLUMN into OUT NOCOPY bind array |
1471 +------------------------------------------*/
1472
1473 dbms_sql.variable_value( p_update_cursor, ':gl_dist_key_value',
1474 gl_dist_array);
1475
1476 /*------------------------------------------------------------+
1477 | Raise the NO_DATA_FOUND exception if no rows were updated |
1478 +------------------------------------------------------------*/
1479
1480 IF (l_count = 0)
1481 THEN RAISE NO_DATA_FOUND;
1482 END IF;
1483
1484 arp_standard.debug('before loop for MRC processing...');
1485 FOR I in gl_dist_array.FIRST .. gl_dist_array.LAST LOOP
1486 /*-----------------------------------------------------+
1487 | call mrc engine to update RA_CUST_TRX_LINES_GL_DIST |
1488 +-----------------------------------------------------*/
1489 arp_standard.debug('before calling maintain_mrc ');
1490 arp_standard.debug('gl dist array('||to_char(I) || ') = ' || to_char(gl_dist_array(I)));
1491
1492 ar_mrc_engine.maintain_mrc_data(
1493 p_event_mode => 'UPDATE',
1494 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
1495 p_mode => 'SINGLE',
1496 p_key_value => gl_dist_array(I));
1497 END LOOP;
1498
1499
1500
1501 arp_util.debug('arp_ctlgd_pkg.generic_update()-');
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.generic_update()');
1506 arp_util.debug(l_update_statement);
1507 arp_util.debug('Error at character: ' ||
1508 to_char(dbms_sql.last_error_position));
1509 RAISE;
1510 END;
1511
1512 /*===========================================================================+
1513 | PROCEDURE |
1514 | set_to_dummy |
1515 | |
1516 | DESCRIPTION |
1517 | This procedure initializes all columns in the parameter dist record |
1518 | to the appropriate dummy value for its datatype. |
1519 | |
1520 | The dummy values are defined in the following package level constants: |
1521 | AR_TEXT_DUMMY |
1522 | AR_FLAG_DUMMY |
1523 | AR_NUMBER_DUMMY |
1524 | AR_DATE_DUMMY |
1525 | |
1526 | SCOPE - PUBLIC |
1527 | |
1528 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1529 | arp_util.debug |
1530 | |
1531 | ARGUMENTS : IN: |
1532 | None |
1533 | OUT: |
1534 | p_dist_rec - The record to initialize |
1535 | |
1536 | RETURNS : NONE |
1537 | |
1538 | NOTES |
1539 | |
1540 | MODIFICATION HISTORY |
1541 | 09-JUN-95 Charlie Tomberg Created |
1542 | |
1543 +===========================================================================*/
1544
1545 PROCEDURE set_to_dummy( p_dist_rec OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype) IS
1546
1547 BEGIN
1548
1549 arp_util.debug('arp_lgd_pkg.set_to_dummy()+');
1550
1551 p_dist_rec.cust_trx_line_gl_dist_id := AR_NUMBER_DUMMY;
1552 p_dist_rec.customer_trx_id := AR_NUMBER_DUMMY;
1553 p_dist_rec.customer_trx_line_id := AR_NUMBER_DUMMY;
1554 p_dist_rec.cust_trx_line_salesrep_id := AR_NUMBER_DUMMY;
1555 p_dist_rec.account_class := AR_TEXT_DUMMY;
1556 p_dist_rec.percent := AR_NUMBER_DUMMY;
1557 p_dist_rec.amount := AR_NUMBER_DUMMY;
1558 p_dist_rec.acctd_amount := AR_NUMBER_DUMMY;
1559 p_dist_rec.gl_date := AR_DATE_DUMMY;
1560 p_dist_rec.original_gl_date := AR_DATE_DUMMY;
1561 p_dist_rec.gl_posted_date := AR_DATE_DUMMY;
1562 p_dist_rec.code_combination_id := AR_NUMBER_DUMMY;
1563 p_dist_rec.concatenated_segments := AR_TEXT_DUMMY;
1564 p_dist_rec.collected_tax_ccid := AR_NUMBER_DUMMY;
1565 p_dist_rec.collected_tax_concat_seg := AR_TEXT_DUMMY;
1566 p_dist_rec.comments := AR_TEXT_DUMMY;
1567 p_dist_rec.account_set_flag := AR_FLAG_DUMMY;
1568 p_dist_rec.latest_rec_flag := AR_FLAG_DUMMY;
1569 p_dist_rec.rec_offset_flag := AR_FLAG_DUMMY; /* bug 3598021 - 3630436 */
1570 p_dist_rec.ussgl_transaction_code := AR_TEXT_DUMMY;
1571 p_dist_rec.ussgl_transaction_code_context := AR_TEXT_DUMMY;
1572 p_dist_rec.attribute_category := AR_TEXT_DUMMY;
1573 p_dist_rec.attribute1 := AR_TEXT_DUMMY;
1574 p_dist_rec.attribute2 := AR_TEXT_DUMMY;
1575 p_dist_rec.attribute3 := AR_TEXT_DUMMY;
1576 p_dist_rec.attribute4 := AR_TEXT_DUMMY;
1577 p_dist_rec.attribute5 := AR_TEXT_DUMMY;
1578 p_dist_rec.attribute6 := AR_TEXT_DUMMY;
1579 p_dist_rec.attribute7 := AR_TEXT_DUMMY;
1580 p_dist_rec.attribute8 := AR_TEXT_DUMMY;
1581 p_dist_rec.attribute9 := AR_TEXT_DUMMY;
1582 p_dist_rec.attribute10 := AR_TEXT_DUMMY;
1583 p_dist_rec.attribute11 := AR_TEXT_DUMMY;
1584 p_dist_rec.attribute12 := AR_TEXT_DUMMY;
1585 p_dist_rec.attribute13 := AR_TEXT_DUMMY;
1586 p_dist_rec.attribute14 := AR_TEXT_DUMMY;
1587 p_dist_rec.attribute15 := AR_TEXT_DUMMY;
1588 p_dist_rec.set_of_books_id := AR_NUMBER_DUMMY;
1589 p_dist_rec.posting_control_id := AR_NUMBER_DUMMY;
1590 p_dist_rec.last_update_date := AR_DATE_DUMMY;
1591 p_dist_rec.last_updated_by := AR_NUMBER_DUMMY;
1592 p_dist_rec.creation_date := AR_DATE_DUMMY;
1593 p_dist_rec.created_by := AR_NUMBER_DUMMY;
1594 p_dist_rec.last_update_login := AR_NUMBER_DUMMY;
1595 p_dist_rec.program_application_id := AR_NUMBER_DUMMY;
1596 p_dist_rec.program_id := AR_NUMBER_DUMMY;
1597 p_dist_rec.program_update_date := AR_DATE_DUMMY;
1598 p_dist_rec.rounding_correction_flag := AR_FLAG_DUMMY;
1599
1600 arp_util.debug('arp_lgd_pkg.set_to_dummy()-');
1601
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 arp_util.debug('EXCEPTION: arp_lgd_pkg.set_to_dummy()');
1605 RAISE;
1606
1607 END;
1608
1609
1610 /*===========================================================================+
1611 | PROCEDURE |
1612 | lock_p |
1613 | |
1614 | DESCRIPTION |
1615 | This procedure locks the ra_cust_trx_line_gl_dist row identified by |
1616 | p_cust_trx_line_gl_dist_id parameter. |
1617 | |
1618 | SCOPE - PUBLIC |
1619 | |
1620 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1621 | arp_util.debug |
1622 | |
1623 | ARGUMENTS : IN: |
1624 | p_cust_trx_line_gl_dist_id - identifies the row to lock |
1625 | OUT: |
1626 | None |
1627 | |
1628 | RETURNS : NONE |
1629 | |
1630 | NOTES |
1631 | |
1632 | MODIFICATION HISTORY |
1633 | 09-JUN-95 Charlie Tomberg Created |
1634 | |
1635 +===========================================================================*/
1636
1637 PROCEDURE lock_p( p_cust_trx_line_gl_dist_id
1638 IN ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type
1639 )
1640 IS
1641
1642 l_cust_trx_line_gl_dist_id
1643 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
1644
1645 BEGIN
1646 arp_util.debug('arp_ctlgd_pkg.lock_p()+');
1647
1648
1649 SELECT cust_trx_line_gl_dist_id
1650 INTO l_cust_trx_line_gl_dist_id
1651 FROM ra_cust_trx_line_gl_dist
1652 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
1653 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1654
1655 arp_util.debug('arp_ctlgd_pkg.lock_p()-');
1656
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_p' );
1660 RAISE;
1661 END;
1662
1663 /*===========================================================================+
1664 | PROCEDURE |
1665 | lock_f_ct_id |
1666 | |
1667 | DESCRIPTION |
1668 | This procedure locks the ra_cust_trx_line_gl_dist rows identified by |
1669 | p_customer_trx_id parameter. |
1670 | |
1671 | SCOPE - PUBLIC |
1672 | |
1673 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1674 | arp_util.debug |
1675 | |
1676 | ARGUMENTS : IN: |
1677 | p_customer_trx_id - identifies the rows to lock |
1678 | p_account_set_flag - value is used to restrict lock |
1679 | p_account_class - value is used to restrict lock |
1680 | OUT: |
1681 | None |
1682 | |
1683 | RETURNS : NONE |
1684 | |
1685 | NOTES |
1686 | |
1687 | MODIFICATION HISTORY |
1688 | 09-JUN-95 Charlie Tomberg Created |
1689 | |
1690 +===========================================================================*/
1691
1692 PROCEDURE lock_f_ct_id( p_customer_trx_id
1693 IN ra_customer_trx.customer_trx_id%type,
1694 p_account_set_flag
1695 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
1696 p_account_class
1697 IN ra_cust_trx_line_gl_dist.account_class%type)
1698 IS
1699
1700
1701 CURSOR lock_c IS
1702 SELECT cust_trx_line_gl_dist_id
1703 FROM ra_cust_trx_line_gl_dist
1704 WHERE customer_trx_id = p_customer_trx_id
1705 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
1706 AND account_class = nvl(p_account_class, account_class)
1707 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1708
1709
1710 BEGIN
1711 arp_util.debug('arp_ctlgd_pkg.lock_f_ct_id()+');
1712
1713 OPEN lock_c;
1714 CLOSE lock_c;
1715
1716 arp_util.debug('arp_ctlgd_pkg.lock_f_ct_id()-');
1717
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_f_ct_id' );
1721 RAISE;
1722 END;
1723
1724 /*===========================================================================+
1725 | PROCEDURE |
1726 | lock_f_ctl_id |
1727 | |
1728 | DESCRIPTION |
1729 | This procedure locks the ra_cust_trx_line_gl_dist rows identified by |
1730 | p_customer_trx_line_id parameter. |
1731 | |
1732 | SCOPE - PUBLIC |
1733 | |
1734 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1735 | arp_util.debug |
1736 | |
1737 | ARGUMENTS : IN: |
1738 | p_customer_trx_line_id - identifies the rows to lock |
1739 | p_account_set_flag - value is used to restrict lock |
1740 | p_account_class - value is used to restrict lock |
1741 | OUT: |
1742 | None |
1743 | |
1744 | RETURNS : NONE |
1745 | |
1746 | NOTES |
1747 | |
1748 | MODIFICATION HISTORY |
1749 | 09-JUN-95 Charlie Tomberg Created |
1750 | |
1751 +===========================================================================*/
1752
1753 PROCEDURE lock_f_ctl_id( p_customer_trx_line_id
1754 IN ra_customer_trx_lines.customer_trx_line_id%type,
1755 p_account_set_flag
1756 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
1757 p_account_class
1758 IN ra_cust_trx_line_gl_dist.account_class%type)
1759 IS
1760
1761 CURSOR lock_c IS
1762 SELECT cust_trx_line_gl_dist_id
1763 FROM ra_cust_trx_line_gl_dist
1764 WHERE customer_trx_line_id = p_customer_trx_line_id
1765 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
1766 AND account_class = nvl(p_account_class, account_class)
1767 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1768
1769 BEGIN
1770 arp_util.debug('arp_ctlgd_pkg.lock_f_ctl_id()+');
1771
1772 OPEN lock_c;
1773 CLOSE lock_c;
1774
1775 arp_util.debug('arp_ctlgd_pkg.lock_f_ctl_id()-');
1776
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_f_ctl_id' );
1780 RAISE;
1781 END;
1782
1783
1784 /*===========================================================================+
1785 | PROCEDURE |
1786 | lock_f_ctls_id |
1787 | |
1788 | DESCRIPTION |
1789 | This procedure locks the ra_cust_trx_line_gl_dist rows identified by |
1790 | p_cust_trx_line_salesrep_id parameter. |
1791 | |
1792 | SCOPE - PUBLIC |
1793 | |
1794 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1795 | arp_util.debug |
1796 | |
1797 | ARGUMENTS : IN: |
1798 | p_cust_trx_line_salesrep_id - identifies the rows to lock |
1799 | p_account_set_flag - value is used to restrict lock |
1800 | p_account_class - value is used to restrict lock |
1801 | OUT: |
1802 | None |
1803 | |
1804 | RETURNS : NONE |
1805 | |
1806 | NOTES |
1807 | |
1808 | MODIFICATION HISTORY |
1809 | 09-JUN-95 Charlie Tomberg Created |
1810 | |
1811 +===========================================================================*/
1812
1813 PROCEDURE lock_f_ctls_id( p_cust_trx_line_salesrep_id
1814 IN ra_cust_trx_line_gl_dist.cust_trx_line_salesrep_id%type,
1815 p_account_set_flag
1816 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
1817 p_account_class
1818 IN ra_cust_trx_line_gl_dist.account_class%type)
1819 IS
1820
1821 CURSOR lock_c IS
1822 SELECT cust_trx_line_gl_dist_id
1823 FROM ra_cust_trx_line_gl_dist
1824 WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
1825 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
1826 AND account_class = nvl(p_account_class, account_class)
1827 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1828
1829 BEGIN
1830 arp_util.debug('arp_ctlgd_pkg.lock_f_ctls_id()+');
1831
1832 OPEN lock_c;
1833 CLOSE lock_c;
1834
1835 arp_util.debug('arp_ctlgd_pkg.lock_f_ctls_id()-');
1836
1837 EXCEPTION
1838 WHEN OTHERS THEN
1839 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_f_ctls_id' );
1840 RAISE;
1841 END;
1842
1843 /*===========================================================================+
1844 | PROCEDURE |
1845 | lock_fetch_p |
1846 | |
1847 | DESCRIPTION |
1848 | This procedure locks the ra_cust_trx_line_gl_dist row identified |
1849 | by the p_cust_trx_line_gl_dist_id parameter and populates the |
1850 | p_dist_rec parameter with the row that was locked. |
1851 | |
1852 | SCOPE - PUBLIC |
1853 | |
1854 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1855 | arp_util.debug |
1856 | |
1857 | ARGUMENTS : IN: |
1858 | p_cust_trx_line_gl_dist_id - identifies the row to lock |
1859 | OUT: |
1860 | p_dist_rec - contains the locked row |
1861 | |
1862 | RETURNS : NONE |
1863 | |
1864 | NOTES |
1865 | |
1866 | MODIFICATION HISTORY |
1867 | 09-JUN-95 Charlie Tomberg Created |
1868 | |
1869 +===========================================================================*/
1870
1871 PROCEDURE lock_fetch_p( p_dist_rec IN OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype,
1872 p_cust_trx_line_gl_dist_id IN
1873 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type
1874 ) IS
1875
1876 BEGIN
1877 arp_util.debug('arp_ctlgd_pkg.lock_fetch_p()+');
1878
1879 SELECT *
1880 INTO p_dist_rec
1881 FROM ra_cust_trx_line_gl_dist
1882 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
1883 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1884
1885 arp_util.debug('arp_ctlgd_pkg.lock_fetch_p()-');
1886
1887 EXCEPTION
1888 WHEN OTHERS THEN
1889 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_fetch_p' );
1890 RAISE;
1891 END;
1892
1893 /*===========================================================================+
1894 | PROCEDURE |
1895 | lock_compare_p |
1896 | |
1897 | DESCRIPTION |
1898 | This procedure locks the ra_cust_trx_line_gl_dist row identified |
1899 | by the p_cust_trx_line_gl_dist_id parameter only if no columns in |
1900 | that row have changed from when they were first selected in the form. |
1901 | |
1902 | SCOPE - PUBLIC |
1903 | |
1904 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1905 | arp_util.debug |
1906 | |
1907 | ARGUMENTS : IN: |
1908 | p_cust_trx_line_gl_dist_id - identifies the row to lock |
1909 | p_dist_rec - dist record for comparison |
1910 | p_ignore_who_flag - directs system to ignore who cols |
1911 | OUT: |
1912 | None |
1913 | |
1914 | RETURNS : NONE |
1915 | |
1916 | NOTES |
1917 | |
1918 | MODIFICATION HISTORY |
1919 | 09-JUN-95 Charlie Tomberg Created |
1920 | 29-JUN-95 Charlie Tomberg Modified to use select for update |
1921 | 13-OCT-95 Martin Johnson Handle NO_DATA_FOUND exception |
1922 | |
1923 +===========================================================================*/
1924
1925 PROCEDURE lock_compare_p( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
1926 p_cust_trx_line_gl_dist_id IN
1927 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
1928 p_ignore_who_flag BOOLEAN DEFAULT FALSE) IS
1929
1930 l_new_dist_rec ra_cust_trx_line_gl_dist%rowtype;
1931 l_temp_dist_rec ra_cust_trx_line_gl_dist%rowtype;
1932 l_ignore_who_flag varchar2(2);
1933
1934 BEGIN
1935 arp_util.debug('arp_ctlgd_pkg.lock_compare_p()+');
1936
1937 IF (p_ignore_who_flag = TRUE)
1938 THEN l_ignore_who_flag := 'Y';
1939 ELSE l_ignore_who_flag := 'N';
1940 END IF;
1941
1942 SELECT *
1943 INTO l_new_dist_rec
1944 FROM ra_cust_trx_line_gl_dist ctlgd
1945 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
1946 AND (
1947 NVL(ctlgd.cust_trx_line_gl_dist_id, AR_NUMBER_DUMMY) =
1948 NVL(
1949 DECODE(p_dist_rec.cust_trx_line_gl_dist_id,
1950 AR_NUMBER_DUMMY, ctlgd.cust_trx_line_gl_dist_id,
1951 p_dist_rec.cust_trx_line_gl_dist_id),
1952 AR_NUMBER_DUMMY
1953 )
1954 AND
1955 NVL(ctlgd.customer_trx_id, AR_NUMBER_DUMMY) =
1956 NVL(
1957 DECODE(p_dist_rec.customer_trx_id,
1958 AR_NUMBER_DUMMY, ctlgd.customer_trx_id,
1959 p_dist_rec.customer_trx_id),
1960 AR_NUMBER_DUMMY
1961 )
1962 AND
1963 NVL(ctlgd.customer_trx_line_id, AR_NUMBER_DUMMY) =
1964 NVL(
1965 DECODE(p_dist_rec.customer_trx_line_id,
1966 AR_NUMBER_DUMMY, ctlgd.customer_trx_line_id,
1967 p_dist_rec.customer_trx_line_id),
1968 AR_NUMBER_DUMMY
1969 )
1970 AND
1971 NVL(ctlgd.cust_trx_line_salesrep_id, AR_NUMBER_DUMMY) =
1972 NVL(
1973 DECODE(p_dist_rec.cust_trx_line_salesrep_id,
1974 AR_NUMBER_DUMMY, ctlgd.cust_trx_line_salesrep_id,
1975 p_dist_rec.cust_trx_line_salesrep_id),
1976 AR_NUMBER_DUMMY
1977 )
1978 AND
1979 NVL(ctlgd.account_class, AR_TEXT_DUMMY) =
1980 NVL(
1981 DECODE(p_dist_rec.account_class,
1982 AR_TEXT_DUMMY, ctlgd.account_class,
1983 p_dist_rec.account_class),
1984 AR_TEXT_DUMMY
1985 )
1986 AND
1987 NVL(ctlgd.percent, AR_NUMBER_DUMMY) =
1988 NVL(
1989 DECODE(p_dist_rec.percent,
1990 AR_NUMBER_DUMMY, ctlgd.percent,
1991 p_dist_rec.percent),
1992 AR_NUMBER_DUMMY
1993 )
1994 AND
1995 NVL(ctlgd.amount, AR_NUMBER_DUMMY) =
1996 NVL(
1997 DECODE(p_dist_rec.amount,
1998 AR_NUMBER_DUMMY, ctlgd.amount,
1999 p_dist_rec.amount),
2000 AR_NUMBER_DUMMY
2001 )
2002 AND
2003 NVL(ctlgd.acctd_amount, AR_NUMBER_DUMMY) =
2004 NVL(
2005 DECODE(p_dist_rec.acctd_amount,
2006 AR_NUMBER_DUMMY, ctlgd.acctd_amount,
2007 p_dist_rec.acctd_amount),
2008 AR_NUMBER_DUMMY
2009 )
2010 AND
2011 NVL(ctlgd.gl_date, AR_DATE_DUMMY) =
2012 NVL(
2013 DECODE(p_dist_rec.gl_date,
2014 AR_DATE_DUMMY, ctlgd.gl_date,
2015 p_dist_rec.gl_date),
2016 AR_DATE_DUMMY
2017 )
2018 AND
2019 NVL(ctlgd.original_gl_date, AR_DATE_DUMMY) =
2020 NVL(
2021 DECODE(p_dist_rec.original_gl_date,
2022 AR_DATE_DUMMY, ctlgd.original_gl_date,
2023 p_dist_rec.original_gl_date),
2024 AR_DATE_DUMMY
2025 )
2026 AND
2027 NVL(ctlgd.gl_posted_date, AR_DATE_DUMMY) =
2028 NVL(
2029 DECODE(p_dist_rec.gl_posted_date,
2030 AR_DATE_DUMMY, ctlgd.gl_posted_date,
2031 p_dist_rec.gl_posted_date),
2032 AR_DATE_DUMMY
2033 )
2034 AND
2035 NVL(ctlgd.code_combination_id, AR_NUMBER_DUMMY) =
2036 NVL(
2037 DECODE(p_dist_rec.code_combination_id,
2038 AR_NUMBER_DUMMY, ctlgd.code_combination_id,
2039 p_dist_rec.code_combination_id),
2040 AR_NUMBER_DUMMY
2041 )
2042 AND
2043 NVL(ctlgd.concatenated_segments, AR_TEXT_DUMMY) =
2044 NVL(
2045 DECODE(p_dist_rec.concatenated_segments,
2046 AR_TEXT_DUMMY, ctlgd.concatenated_segments,
2047 p_dist_rec.concatenated_segments),
2048 AR_TEXT_DUMMY
2049 )
2050 AND
2051 NVL(ctlgd.collected_tax_ccid, AR_NUMBER_DUMMY) =
2052 NVL(
2053 DECODE(p_dist_rec.collected_tax_ccid,
2054 AR_NUMBER_DUMMY, ctlgd.collected_tax_ccid,
2055 p_dist_rec.collected_tax_ccid),
2056 AR_NUMBER_DUMMY
2057 )
2058 AND
2059 NVL(ctlgd.collected_tax_concat_seg, AR_TEXT_DUMMY) =
2060 NVL(
2061 DECODE(p_dist_rec.collected_tax_concat_seg,
2062 AR_TEXT_DUMMY, ctlgd.collected_tax_concat_seg,
2063 p_dist_rec.collected_tax_concat_seg),
2064 AR_TEXT_DUMMY
2065 )
2066 AND
2067 NVL(ctlgd.comments, AR_TEXT_DUMMY) =
2068 NVL(
2069 DECODE(p_dist_rec.comments,
2070 AR_TEXT_DUMMY, ctlgd.comments,
2071 p_dist_rec.comments),
2072 AR_TEXT_DUMMY
2073 )
2074 AND
2075 NVL(ctlgd.account_set_flag, AR_FLAG_DUMMY) =
2076 NVL(
2077 DECODE(p_dist_rec.account_set_flag,
2078 AR_FLAG_DUMMY, ctlgd.account_set_flag,
2079 p_dist_rec.account_set_flag),
2080 AR_FLAG_DUMMY
2081 )
2082 AND
2083 NVL(ctlgd.latest_rec_flag, AR_FLAG_DUMMY) =
2084 NVL(
2085 DECODE(p_dist_rec.latest_rec_flag,
2086 AR_FLAG_DUMMY, ctlgd.latest_rec_flag,
2087 p_dist_rec.latest_rec_flag),
2088 AR_FLAG_DUMMY
2089 )
2090 AND
2091 NVL(ctlgd.ussgl_transaction_code, AR_TEXT_DUMMY) =
2092 NVL(
2093 DECODE(p_dist_rec.ussgl_transaction_code,
2094 AR_TEXT_DUMMY, ctlgd.ussgl_transaction_code,
2095 p_dist_rec.ussgl_transaction_code),
2096 AR_TEXT_DUMMY
2097 )
2098 AND
2099 NVL(ctlgd.ussgl_transaction_code_context, AR_TEXT_DUMMY) =
2100 NVL(
2101 DECODE(p_dist_rec.ussgl_transaction_code_context,
2102 AR_TEXT_DUMMY, ctlgd.ussgl_transaction_code_context,
2103 p_dist_rec.ussgl_transaction_code_context),
2104 AR_TEXT_DUMMY
2105 )
2106 AND
2107 NVL(ctlgd.attribute_category, AR_TEXT_DUMMY) =
2108 NVL(
2109 DECODE(p_dist_rec.attribute_category,
2110 AR_TEXT_DUMMY, ctlgd.attribute_category,
2111 p_dist_rec.attribute_category),
2112 AR_TEXT_DUMMY
2113 )
2114 AND
2115 NVL(ctlgd.attribute1, AR_TEXT_DUMMY) =
2116 NVL(
2117 DECODE(p_dist_rec.attribute1,
2118 AR_TEXT_DUMMY, ctlgd.attribute1,
2119 p_dist_rec.attribute1),
2120 AR_TEXT_DUMMY
2121 )
2122 AND
2123 NVL(ctlgd.attribute2, AR_TEXT_DUMMY) =
2124 NVL(
2125 DECODE(p_dist_rec.attribute2,
2126 AR_TEXT_DUMMY, ctlgd.attribute2,
2127 p_dist_rec.attribute2),
2128 AR_TEXT_DUMMY
2129 )
2130 AND
2131 NVL(ctlgd.attribute3, AR_TEXT_DUMMY) =
2132 NVL(
2133 DECODE(p_dist_rec.attribute3,
2134 AR_TEXT_DUMMY, ctlgd.attribute3,
2135 p_dist_rec.attribute3),
2136 AR_TEXT_DUMMY
2137 )
2138 AND
2139 NVL(ctlgd.attribute4, AR_TEXT_DUMMY) =
2140 NVL(
2141 DECODE(p_dist_rec.attribute4,
2142 AR_TEXT_DUMMY, ctlgd.attribute4,
2143 p_dist_rec.attribute4),
2144 AR_TEXT_DUMMY
2145 )
2146 AND
2147 NVL(ctlgd.attribute5, AR_TEXT_DUMMY) =
2148 NVL(
2149 DECODE(p_dist_rec.attribute5,
2150 AR_TEXT_DUMMY, ctlgd.attribute5,
2151 p_dist_rec.attribute5),
2152 AR_TEXT_DUMMY
2153 )
2154 AND
2155 NVL(ctlgd.attribute6, AR_TEXT_DUMMY) =
2156 NVL(
2157 DECODE(p_dist_rec.attribute6,
2158 AR_TEXT_DUMMY, ctlgd.attribute6,
2159 p_dist_rec.attribute6),
2160 AR_TEXT_DUMMY
2161 )
2162 AND
2163 NVL(ctlgd.attribute7, AR_TEXT_DUMMY) =
2164 NVL(
2165 DECODE(p_dist_rec.attribute7,
2166 AR_TEXT_DUMMY, ctlgd.attribute7,
2167 p_dist_rec.attribute7),
2168 AR_TEXT_DUMMY
2169 )
2170 AND
2171 NVL(ctlgd.attribute8, AR_TEXT_DUMMY) =
2172 NVL(
2173 DECODE(p_dist_rec.attribute8,
2174 AR_TEXT_DUMMY, ctlgd.attribute8,
2175 p_dist_rec.attribute8),
2176 AR_TEXT_DUMMY
2177 )
2178 AND
2179 NVL(ctlgd.attribute9, AR_TEXT_DUMMY) =
2180 NVL(
2181 DECODE(p_dist_rec.attribute9,
2182 AR_TEXT_DUMMY, ctlgd.attribute9,
2183 p_dist_rec.attribute9),
2184 AR_TEXT_DUMMY
2185 )
2186 AND
2187 NVL(ctlgd.attribute10, AR_TEXT_DUMMY) =
2188 NVL(
2189 DECODE(p_dist_rec.attribute10,
2190 AR_TEXT_DUMMY, ctlgd.attribute10,
2191 p_dist_rec.attribute10),
2192 AR_TEXT_DUMMY
2193 )
2194 AND
2195 NVL(ctlgd.attribute11, AR_TEXT_DUMMY) =
2196 NVL(
2197 DECODE(p_dist_rec.attribute11,
2198 AR_TEXT_DUMMY, ctlgd.attribute11,
2199 p_dist_rec.attribute11),
2200 AR_TEXT_DUMMY
2201 )
2202 AND
2203 NVL(ctlgd.attribute12, AR_TEXT_DUMMY) =
2204 NVL(
2205 DECODE(p_dist_rec.attribute12,
2206 AR_TEXT_DUMMY, ctlgd.attribute12,
2207 p_dist_rec.attribute12),
2208 AR_TEXT_DUMMY
2209 )
2210 AND
2211 NVL(ctlgd.attribute13, AR_TEXT_DUMMY) =
2212 NVL(
2213 DECODE(p_dist_rec.attribute13,
2214 AR_TEXT_DUMMY, ctlgd.attribute13,
2215 p_dist_rec.attribute13),
2216 AR_TEXT_DUMMY
2217 )
2218 AND
2219 NVL(ctlgd.attribute14, AR_TEXT_DUMMY) =
2220 NVL(
2221 DECODE(p_dist_rec.attribute14,
2222 AR_TEXT_DUMMY, ctlgd.attribute14,
2223 p_dist_rec.attribute14),
2224 AR_TEXT_DUMMY
2225 )
2226 AND
2227 NVL(ctlgd.attribute15, AR_TEXT_DUMMY) =
2228 NVL(
2229 DECODE(p_dist_rec.attribute15,
2230 AR_TEXT_DUMMY, ctlgd.attribute15,
2231 p_dist_rec.attribute15),
2232 AR_TEXT_DUMMY
2233 )
2234 AND
2235 NVL(ctlgd.set_of_books_id, AR_NUMBER_DUMMY) =
2236 NVL(
2237 DECODE(p_dist_rec.set_of_books_id,
2238 AR_NUMBER_DUMMY, ctlgd.set_of_books_id,
2239 p_dist_rec.set_of_books_id),
2240 AR_NUMBER_DUMMY
2241 )
2242 AND
2243 NVL(ctlgd.posting_control_id, AR_NUMBER_DUMMY) =
2244 NVL(
2245 DECODE(p_dist_rec.posting_control_id,
2246 AR_NUMBER_DUMMY, ctlgd.posting_control_id,
2247 p_dist_rec.posting_control_id),
2248 AR_NUMBER_DUMMY
2249 )
2250 AND
2251 NVL(ctlgd.last_update_date, AR_DATE_DUMMY) =
2252 NVL(
2253 DECODE(l_ignore_who_flag,
2254 'Y', NVL(ctlgd.last_update_date, AR_DATE_DUMMY),
2255 DECODE(
2256 p_dist_rec.last_update_date,
2257 AR_DATE_DUMMY, ctlgd.last_update_date,
2258 p_dist_rec.last_update_date
2259 )
2260 ),
2261 AR_DATE_DUMMY
2262 )
2263 AND
2264 NVL(ctlgd.last_updated_by, AR_NUMBER_DUMMY) =
2265 NVL(
2266 DECODE(l_ignore_who_flag,
2267 'Y', NVL(ctlgd.last_updated_by, AR_NUMBER_DUMMY),
2268 DECODE(
2269 p_dist_rec.last_updated_by,
2270 AR_NUMBER_DUMMY, ctlgd.last_updated_by,
2271 p_dist_rec.last_updated_by
2272 )
2273 ),
2274 AR_NUMBER_DUMMY
2275 )
2276 AND
2277 NVL(ctlgd.creation_date, AR_DATE_DUMMY) =
2278 NVL(
2279 DECODE(l_ignore_who_flag,
2280 'Y', NVL(ctlgd.creation_date, AR_DATE_DUMMY),
2281 DECODE(
2282 p_dist_rec.creation_date,
2283 AR_DATE_DUMMY, ctlgd.creation_date,
2284 p_dist_rec.creation_date
2285 )
2286 ),
2287 AR_DATE_DUMMY
2288 )
2289 AND
2290 NVL(ctlgd.created_by, AR_NUMBER_DUMMY) =
2291 NVL(
2292 DECODE(l_ignore_who_flag,
2293 'Y', NVL(ctlgd.created_by, AR_NUMBER_DUMMY),
2294 DECODE(
2295 p_dist_rec.created_by,
2296 AR_NUMBER_DUMMY, ctlgd.created_by,
2297 p_dist_rec.created_by
2298 )
2299 ),
2300 AR_NUMBER_DUMMY
2301 )
2302 AND
2303 NVL(ctlgd.last_update_login, AR_NUMBER_DUMMY) =
2304 NVL(
2305 DECODE(l_ignore_who_flag,
2306 'Y', NVL(ctlgd.last_update_login, AR_NUMBER_DUMMY),
2307 DECODE(
2308 p_dist_rec.last_update_login,
2309 AR_NUMBER_DUMMY, ctlgd.last_update_login,
2310 p_dist_rec.last_update_login
2311 )
2312 ),
2313 AR_NUMBER_DUMMY
2314 )
2315 AND
2316 NVL(ctlgd.program_application_id, AR_NUMBER_DUMMY) =
2317 NVL(
2318 DECODE(l_ignore_who_flag,
2319 'Y', NVL(ctlgd.program_application_id, AR_NUMBER_DUMMY),
2320 DECODE(
2321 p_dist_rec.program_application_id,
2322 AR_NUMBER_DUMMY, ctlgd.program_application_id,
2323 p_dist_rec.program_application_id
2324 )
2325 ),
2326 AR_NUMBER_DUMMY
2327 )
2328 AND
2329 NVL(ctlgd.program_id, AR_NUMBER_DUMMY) =
2330 NVL(
2331 DECODE(l_ignore_who_flag,
2332 'Y', NVL(ctlgd.program_id, AR_NUMBER_DUMMY),
2333 DECODE(
2334 p_dist_rec.program_id,
2335 AR_NUMBER_DUMMY, ctlgd.program_id,
2336 p_dist_rec.program_id
2337 )
2338 ),
2339 AR_NUMBER_DUMMY
2340 )
2341 AND
2342 NVL(ctlgd.program_update_date, AR_DATE_DUMMY) =
2343 NVL(
2344 DECODE(l_ignore_who_flag,
2345 'Y', NVL(ctlgd.program_update_date, AR_DATE_DUMMY),
2346 DECODE(
2347 p_dist_rec.program_update_date,
2348 AR_DATE_DUMMY, ctlgd.program_update_date,
2349 p_dist_rec.program_update_date
2350 )
2351 ),
2352 AR_DATE_DUMMY
2353 )
2354 AND
2355 NVL(ctlgd.ccid_change_flag, AR_FLAG_DUMMY) =
2356 NVL(
2357 DECODE(p_dist_rec.ccid_change_flag,
2358 AR_FLAG_DUMMY, ctlgd.ccid_change_flag,
2359 p_dist_rec.ccid_change_flag),
2360 AR_FLAG_DUMMY
2361 ) /* Bug 8788491 */
2362 )
2363 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
2364
2365 arp_util.debug('arp_ctlgd_pkg.lock_compare_p()-');
2366
2367 EXCEPTION
2368 WHEN NO_DATA_FOUND THEN
2369
2370 arp_util.debug('');
2371 arp_util.debug('p_cust_trx_line_gl_dist_id = ' ||
2372 p_cust_trx_line_gl_dist_id );
2373 arp_util.debug('-------- new dist record --------');
2374 display_dist_rec( p_dist_rec );
2375
2376 arp_util.debug('');
2377
2378 arp_util.debug('-------- old dist record --------');
2379
2380 fetch_p( l_temp_dist_rec,
2381 p_cust_trx_line_gl_dist_id );
2382
2383 display_dist_rec( l_temp_dist_rec );
2384
2385 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2386 APP_EXCEPTION.Raise_Exception;
2387
2388
2389 WHEN OTHERS THEN
2390 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_compare_p' );
2391
2392 arp_util.debug( SQLERRM );
2393
2394 arp_util.debug('----- parameters for lock_compare_p -----');
2395
2396 arp_util.debug('p_cust_trx_line_gl_dist_id = ' ||
2397 p_cust_trx_line_gl_dist_id );
2398 arp_util.debug('p_ignore_who_flag =' ||
2399 arp_trx_util.boolean_to_varchar2(p_ignore_who_flag));
2400
2401 arp_util.debug('');
2402 arp_util.debug('-------- new dist record --------');
2403 display_dist_rec( p_dist_rec );
2404
2405 arp_util.debug('');
2406
2407 arp_util.debug('-------- old dist record --------');
2408
2409 fetch_p( l_temp_dist_rec,
2410 p_cust_trx_line_gl_dist_id );
2411
2412 display_dist_rec( l_temp_dist_rec );
2413
2414 RAISE;
2415 END;
2416
2417 /*===========================================================================+
2418 | PROCEDURE |
2419 | lock_compare_cover |
2420 | |
2421 | DESCRIPTION |
2422 | Converts column parameters to a dist record and |
2423 | lockss a dist line. |
2424 | |
2425 | SCOPE - PUBLIC |
2426 | |
2427 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2428 | arp_util.debug |
2429 | |
2430 | ARGUMENTS : IN: |
2431 | p_cust_trx_line_gl_dist_id |
2432 | p_customer_trx_id |
2433 | p_customer_trx_line_id |
2434 | p_cust_trx_line_salesrep_id |
2435 | p_account_class |
2436 | p_percent |
2437 | p_amount |
2438 | p_acctd_amount |
2439 | p_gl_date |
2440 | p_original_gl_date |
2441 | p_gl_posted_date |
2442 | p_code_combination_id |
2443 | p_concatenated_segments |
2444 | p_collected_tax_ccid |
2445 | p_collected_tax_concat_seg |
2446 | p_comments |
2447 | p_account_set_flag |
2448 | p_latest_rec_flag |
2449 | p_ussgl_transaction_code |
2450 | p_ussgl_trx_code_context |
2451 | p_attribute_category |
2452 | p_attribute1 |
2453 | p_attribute2 |
2454 | p_attribute3 |
2455 | p_attribute4 |
2456 | p_attribute5 |
2457 | p_attribute6 |
2458 | p_attribute7 |
2459 | p_attribute8 |
2460 | p_attribute9 |
2461 | p_attribute10 |
2462 | p_attribute11 |
2463 | p_attribute12 |
2464 | p_attribute13 |
2465 | p_attribute14 |
2466 | p_attribute15 |
2467 | p_posting_control_id |
2468 | OUT: |
2469 | None |
2470 | IN/ OUT: |
2471 | None |
2472 | |
2473 | RETURNS : NONE |
2474 | |
2475 | NOTES |
2476 | |
2477 | MODIFICATION HISTORY |
2478 | 13-OCT-95 Martin Johnson Created |
2479 | |
2480 +===========================================================================*/
2481
2482
2483 PROCEDURE lock_compare_cover(
2484 p_cust_trx_line_gl_dist_id IN
2485 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
2486 p_customer_trx_id IN
2487 ra_cust_trx_line_gl_dist.customer_trx_id%type,
2488 p_customer_trx_line_id IN
2489 ra_cust_trx_line_gl_dist.customer_trx_line_id %type,
2490 p_cust_trx_line_salesrep_id IN
2491 ra_cust_trx_line_gl_dist.cust_trx_line_salesrep_id%type,
2492 p_account_class IN
2493 ra_cust_trx_line_gl_dist.account_class%type,
2494 p_percent IN
2495 ra_cust_trx_line_gl_dist.percent%type,
2496 p_amount IN
2497 ra_cust_trx_line_gl_dist.amount%type,
2498 p_gl_date IN
2499 ra_cust_trx_line_gl_dist.gl_date%type,
2500 p_original_gl_date IN
2501 ra_cust_trx_line_gl_dist.original_gl_date%type,
2502 p_gl_posted_date IN
2503 ra_cust_trx_line_gl_dist.gl_posted_date%type,
2504 p_code_combination_id IN
2505 ra_cust_trx_line_gl_dist.code_combination_id%type,
2506 p_concatenated_segments IN
2507 ra_cust_trx_line_gl_dist.concatenated_segments%type,
2508 p_collected_tax_ccid IN
2509 ra_cust_trx_line_gl_dist.collected_tax_ccid%type,
2510 p_collected_tax_concat_seg IN
2511 ra_cust_trx_line_gl_dist.collected_tax_concat_seg%type,
2512 p_comments IN
2513 ra_cust_trx_line_gl_dist.comments%type,
2514 p_account_set_flag IN
2515 ra_cust_trx_line_gl_dist.account_set_flag%type,
2516 p_latest_rec_flag IN
2517 ra_cust_trx_line_gl_dist.latest_rec_flag%type,
2518 p_ussgl_transaction_code IN
2519 ra_cust_trx_line_gl_dist.ussgl_transaction_code%type,
2520 p_ussgl_trx_code_context IN
2521 ra_cust_trx_line_gl_dist.ussgl_transaction_code_context%type,
2522 p_attribute_category IN
2523 ra_cust_trx_line_gl_dist.attribute_category%type,
2524 p_attribute1 IN
2525 ra_cust_trx_line_gl_dist.attribute1%type,
2526 p_attribute2 IN
2527 ra_cust_trx_line_gl_dist.attribute2%type,
2528 p_attribute3 IN
2529 ra_cust_trx_line_gl_dist.attribute3%type,
2530 p_attribute4 IN
2531 ra_cust_trx_line_gl_dist.attribute4%type,
2532 p_attribute5 IN
2533 ra_cust_trx_line_gl_dist.attribute5%type,
2534 p_attribute6 IN
2535 ra_cust_trx_line_gl_dist.attribute6%type,
2536 p_attribute7 IN
2537 ra_cust_trx_line_gl_dist.attribute7%type,
2538 p_attribute8 IN
2539 ra_cust_trx_line_gl_dist.attribute8%type,
2540 p_attribute9 IN
2541 ra_cust_trx_line_gl_dist.attribute9%type,
2542 p_attribute10 IN
2543 ra_cust_trx_line_gl_dist.attribute10%type,
2544 p_attribute11 IN
2545 ra_cust_trx_line_gl_dist.attribute11%type,
2546 p_attribute12 IN
2547 ra_cust_trx_line_gl_dist.attribute12%type,
2548 p_attribute13 IN
2549 ra_cust_trx_line_gl_dist.attribute13%type,
2550 p_attribute14 IN
2551 ra_cust_trx_line_gl_dist.attribute14%type,
2552 p_attribute15 IN
2553 ra_cust_trx_line_gl_dist.attribute15%type,
2554 p_posting_control_id IN
2555 ra_cust_trx_line_gl_dist.posting_control_id%type,
2556 p_ccid_change_flag IN
2557 ra_cust_trx_line_gl_dist.ccid_change_flag%type ) /* Bug 8788491 */
2558 IS
2559
2560 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
2561
2562 BEGIN
2563
2564 IF PG_DEBUG in ('Y', 'C') THEN
2565 arp_util.debug('arp_ctlgd_pkg.lock_compare_cover()+',
2566 pg_msg_level_debug);
2567 END IF;
2568
2569 /*------------------------------------------------+
2570 | Populate the dist record group with |
2571 | the values passed in as parameters. |
2572 +------------------------------------------------*/
2573
2574 set_to_dummy(l_dist_rec);
2575
2576 l_dist_rec.cust_trx_line_gl_dist_id := p_cust_trx_line_gl_dist_id;
2577 l_dist_rec.customer_trx_id := p_customer_trx_id;
2578 l_dist_rec.customer_trx_line_id := p_customer_trx_line_id;
2579 l_dist_rec.cust_trx_line_salesrep_id := p_cust_trx_line_salesrep_id;
2580 l_dist_rec.account_class := p_account_class;
2581 l_dist_rec.percent := p_percent;
2582 l_dist_rec.amount := p_amount;
2583 l_dist_rec.gl_date := p_gl_date;
2584 l_dist_rec.original_gl_date := p_original_gl_date;
2585 l_dist_rec.gl_posted_date := p_gl_posted_date;
2586 l_dist_rec.code_combination_id := p_code_combination_id;
2587 l_dist_rec.concatenated_segments := p_concatenated_segments;
2588 l_dist_rec.collected_tax_ccid := p_collected_tax_ccid;
2589 l_dist_rec.collected_tax_concat_seg := p_collected_tax_concat_seg;
2590 l_dist_rec.comments := p_comments;
2591 l_dist_rec.account_set_flag := p_account_set_flag;
2592 l_dist_rec.latest_rec_flag := p_latest_rec_flag;
2593 l_dist_rec.ussgl_transaction_code := p_ussgl_transaction_code;
2594 l_dist_rec.ussgl_transaction_code_context := p_ussgl_trx_code_context;
2595 l_dist_rec.attribute_category := p_attribute_category;
2596 l_dist_rec.attribute1 := p_attribute1;
2597 l_dist_rec.attribute2 := p_attribute2;
2598 l_dist_rec.attribute3 := p_attribute3;
2599 l_dist_rec.attribute4 := p_attribute4;
2600 l_dist_rec.attribute5 := p_attribute5;
2601 l_dist_rec.attribute6 := p_attribute6;
2602 l_dist_rec.attribute7 := p_attribute7;
2603 l_dist_rec.attribute8 := p_attribute8;
2604 l_dist_rec.attribute9 := p_attribute9;
2605 l_dist_rec.attribute10 := p_attribute10;
2606 l_dist_rec.attribute11 := p_attribute11;
2607 l_dist_rec.attribute12 := p_attribute12;
2608 l_dist_rec.attribute13 := p_attribute13;
2609 l_dist_rec.attribute14 := p_attribute14;
2610 l_dist_rec.attribute15 := p_attribute15;
2611 l_dist_rec.posting_control_id := p_posting_control_id;
2612 l_dist_rec.ccid_change_flag := p_ccid_change_flag; /* Bug 8788491 */
2613
2614 /*----------------------------------------------+
2615 | Call the standard dist table handler |
2616 +----------------------------------------------*/
2617
2618 lock_compare_p(
2619 l_dist_rec,
2620 p_cust_trx_line_gl_dist_id,
2621 TRUE -- ignore who columns
2622 );
2623
2624 IF PG_DEBUG in ('Y', 'C') THEN
2625 arp_util.debug('arp_ctlgd_pkg.lock_compare_cover()-',
2626 pg_msg_level_debug);
2627 END IF;
2628
2629 EXCEPTION
2630 WHEN OTHERS THEN
2631
2632 IF PG_DEBUG in ('Y', 'C') THEN
2633 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.lock_compare_cover()',
2634 pg_msg_level_debug);
2635 arp_util.debug('lock_compare_cover: ' || '------- parameters for lock_compare_cove() ' ||
2636 '---------',
2637 pg_msg_level_debug);
2638 arp_util.debug('lock_compare_cover: ' || 'p_cust_trx_line_gl_dist_id = ' ||
2639 p_cust_trx_line_gl_dist_id,
2640 pg_msg_level_debug);
2641 arp_util.debug('lock_compare_cover: ' || 'p_customer_trx_id = ' || p_customer_trx_id,
2642 pg_msg_level_debug);
2643 arp_util.debug('lock_compare_cover: ' || 'p_customer_trx_line_id = ' || p_customer_trx_line_id,
2644 pg_msg_level_debug);
2645 arp_util.debug('lock_compare_cover: ' || 'p_cust_trx_line_salesrep_id = ' ||
2646 p_cust_trx_line_salesrep_id,
2647 pg_msg_level_debug);
2648 arp_util.debug('lock_compare_cover: ' || 'p_account_class = ' || p_account_class,
2649 pg_msg_level_debug);
2650 arp_util.debug('lock_compare_cover: ' || 'p_percent = ' || p_percent,
2651 pg_msg_level_debug);
2652 arp_util.debug('lock_compare_cover: ' || 'p_amount = ' || p_amount,
2653 pg_msg_level_debug);
2654 arp_util.debug('lock_compare_cover: ' || 'p_gl_date = ' || p_gl_date,
2655 pg_msg_level_debug);
2656 arp_util.debug('lock_compare_cover: ' || 'p_gl_posted_date = ' || p_gl_posted_date,
2657 pg_msg_level_debug);
2658 arp_util.debug('lock_compare_cover: ' || 'p_original_gl_date = ' || p_original_gl_date,
2659 pg_msg_level_debug);
2660 arp_util.debug('lock_compare_cover: ' || 'p_code_combination_id = ' || p_code_combination_id,
2661 pg_msg_level_debug);
2662 arp_util.debug('lock_compare_cover: ' || 'p_concatenated_segments = ' || p_concatenated_segments,
2663 pg_msg_level_debug);
2664 arp_util.debug('lock_compare_cover: ' || 'p_collected_tax_ccid = ' || p_collected_tax_ccid,
2665 pg_msg_level_debug);
2666 arp_util.debug('lock_compare_cover: ' || 'p_collected_tax_concat_seg = ' || p_collected_tax_concat_seg,
2667 pg_msg_level_debug);
2668 arp_util.debug('lock_compare_cover: ' || 'p_comments = ' || p_comments,
2669 pg_msg_level_debug);
2670 arp_util.debug('lock_compare_cover: ' || 'p_account_set_flag = ' || p_account_set_flag,
2671 pg_msg_level_debug);
2672 arp_util.debug('lock_compare_cover: ' || 'p_latest_rec_flag = ' || p_latest_rec_flag,
2673 pg_msg_level_debug);
2674 arp_util.debug('lock_compare_cover: ' || 'p_ussgl_transaction_code = ' ||
2675 p_ussgl_transaction_code,
2676 pg_msg_level_debug);
2677 arp_util.debug('lock_compare_cover: ' || 'p_ussgl_trx_code_context = ' ||
2678 p_ussgl_trx_code_context,
2679 pg_msg_level_debug);
2680 arp_util.debug('lock_compare_cover: ' || 'p_attribute_category = ' || p_attribute_category,
2681 pg_msg_level_debug);
2682 arp_util.debug('lock_compare_cover: ' || 'p_attribute1 = ' || p_attribute1,
2683 pg_msg_level_debug);
2684 arp_util.debug('lock_compare_cover: ' || 'p_attribute2 = ' || p_attribute2,
2685 pg_msg_level_debug);
2686 arp_util.debug('lock_compare_cover: ' || 'p_attribute3 = ' || p_attribute3,
2687 pg_msg_level_debug);
2688 arp_util.debug('lock_compare_cover: ' || 'p_attribute4 = ' || p_attribute4,
2689 pg_msg_level_debug);
2690 arp_util.debug('lock_compare_cover: ' || 'p_attribute5 = ' || p_attribute5,
2691 pg_msg_level_debug);
2692 arp_util.debug('lock_compare_cover: ' || 'p_attribute6 = ' || p_attribute6,
2693 pg_msg_level_debug);
2694 arp_util.debug('lock_compare_cover: ' || 'p_attribute7 = ' || p_attribute7,
2695 pg_msg_level_debug);
2696 arp_util.debug('lock_compare_cover: ' || 'p_attribute8 = ' || p_attribute8,
2697 pg_msg_level_debug);
2698 arp_util.debug('lock_compare_cover: ' || 'p_attribute9 = ' || p_attribute9,
2699 pg_msg_level_debug);
2700 arp_util.debug('lock_compare_cover: ' || 'p_attribute10 = ' || p_attribute10,
2701 pg_msg_level_debug);
2702 arp_util.debug('lock_compare_cover: ' || 'p_attribute11 = ' || p_attribute11,
2703 pg_msg_level_debug);
2704 arp_util.debug('lock_compare_cover: ' || 'p_attribute12 = ' || p_attribute12,
2705 pg_msg_level_debug);
2706 arp_util.debug('lock_compare_cover: ' || 'p_attribute13 = ' || p_attribute13,
2707 pg_msg_level_debug);
2708 arp_util.debug('lock_compare_cover: ' || 'p_attribute14 = ' || p_attribute14,
2709 pg_msg_level_debug);
2710 arp_util.debug('lock_compare_cover: ' || 'p_attribute15 = ' || p_attribute15,
2711 pg_msg_level_debug);
2712 arp_util.debug('lock_compare_cover: ' || 'p_posting_control_id = ' || p_posting_control_id,
2713 pg_msg_level_debug);
2714 END IF;
2715
2716 RAISE;
2717
2718 END lock_compare_cover;
2719
2720 /*===========================================================================+
2721 | PROCEDURE |
2722 | fetch_p |
2723 | |
2724 | DESCRIPTION |
2725 | This procedure fetches a single row from ra_cust_trx_line_gl_dist |
2726 | into a variable specified as a parameter based on the table's primary |
2727 | key, cust_trx_line_gl_dist_id |
2728 | |
2729 | SCOPE - PUBLIC |
2730 | |
2731 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2732 | arp_util.debug |
2733 | |
2734 | ARGUMENTS : IN: |
2735 | p_cust_trx_line_gl_dist_id - identifies the record to fetch |
2736 | OUT: |
2737 | p_dist_rec - contains the fetched record |
2738 | |
2739 | RETURNS : NONE |
2740 | |
2741 | NOTES |
2742 | |
2743 | MODIFICATION HISTORY |
2744 | 09-JUN-95 Charlie Tomberg Created |
2745 | |
2746 +===========================================================================*/
2747
2748 PROCEDURE fetch_p( p_dist_rec OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype,
2749 p_cust_trx_line_gl_dist_id IN
2750 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type)
2751 IS
2752
2753 BEGIN
2754 arp_util.debug('arp_ctlgd_pkg.fetch_p()+');
2755
2756 SELECT *
2757 INTO p_dist_rec
2758 FROM ra_cust_trx_line_gl_dist
2759 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id;
2760
2761 arp_util.debug('arp_ctlgd_pkg.fetch_p()-');
2762
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.fetch_p' );
2766 RAISE;
2767 END;
2768
2769
2770 /*===========================================================================+
2771 | PROCEDURE |
2772 | delete_p |
2773 | |
2774 | DESCRIPTION |
2775 | This procedure deletes the ra_cust_trx_line_gl_dist row identified |
2776 | by the p_cust_trx_line_gl_dist_id parameter. |
2777 | |
2778 | SCOPE - PUBLIC |
2779 | |
2780 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2781 | arp_util.debug |
2782 | |
2783 | ARGUMENTS : IN: |
2784 | p_cust_trx_line_gl_dist_id - identifies the rows to delete |
2785 | OUT: |
2786 | None |
2787 | |
2788 | RETURNS : NONE |
2789 | |
2790 | NOTES |
2791 | |
2792 | MODIFICATION HISTORY |
2793 | 09-JUN-95 Charlie Tomberg Created |
2794 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2795 | added processing calls for delete from |
2796 | ra_cust_trx_lines_gl_dist |
2797 | |
2798 +===========================================================================*/
2799
2800 procedure delete_p( p_cust_trx_line_gl_dist_id
2801 IN ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type)
2802 IS
2803
2804
2805 BEGIN
2806
2807
2808 arp_util.debug('arp_ctlgd_pkg.delete_p()+');
2809
2810 DELETE FROM ra_cust_trx_line_gl_dist
2811 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id;
2812
2813 IF ( SQL%ROWCOUNT = 0 )
2814 THEN arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_p()');
2815 RAISE NO_DATA_FOUND;
2816 END IF;
2817
2818 /* call mrc api to delete */
2819 arp_standard.debug('calling mrc engine for insertion of gl dist data');
2820 ar_mrc_engine.maintain_mrc_data(
2821 p_event_mode => 'DELETE',
2822 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2823 p_mode => 'SINGLE',
2824 p_key_value => p_cust_trx_line_gl_dist_id);
2825
2826
2827 arp_util.debug('arp_ctlgd_pkg.delete_p()-');
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_p()');
2832
2833 RAISE;
2834
2835 END;
2836
2837 /*===========================================================================+
2838 | PROCEDURE |
2839 | delete_f_ct_id |
2840 | |
2841 | DESCRIPTION |
2842 | This procedure deletes the ra_cust_trx_line_gl_dist rows identified |
2843 | by the p_customer_trx_id parameter. |
2844 | |
2845 | SCOPE - PUBLIC |
2846 | |
2847 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2848 | arp_util.debug |
2849 | |
2850 | ARGUMENTS : IN: |
2851 | p_customer_trx_id - identifies the rows to delete |
2852 | p_account_set_flag - value is used to restrict delete |
2853 | p_account_class - value is used to restrict delete |
2854 | OUT: |
2855 | None |
2856 | |
2857 | RETURNS : NONE |
2858 | |
2859 | NOTES |
2860 | |
2861 | MODIFICATION HISTORY |
2862 | 09-JUN-95 Charlie Tomberg Created |
2863 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2864 | added processing calls for delete from |
2865 | ra_cust_trx_lines_gl_dist |
2866 | |
2867 +===========================================================================*/
2868
2869 procedure delete_f_ct_id( p_customer_trx_id
2870 IN ra_customer_trx.customer_trx_id%type,
2871 p_account_set_flag
2872 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
2873 p_account_class
2874 IN ra_cust_trx_line_gl_dist.account_class%type)
2875 IS
2876
2877 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2878
2879 BEGIN
2880
2881 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_id()+');
2882
2883 DELETE FROM ra_cust_trx_line_gl_dist
2884 WHERE customer_trx_id = p_customer_trx_id
2885 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
2886 AND account_class = nvl(p_account_class, account_class);
2887
2888 arp_standard.debug('calling mrc engine for insertion of gl dist data');
2889 ar_mrc_engine.maintain_mrc_data(
2890 p_event_mode => 'DELETE',
2891 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2892 p_mode => 'BATCH',
2893 p_key_value_list => l_gl_dist_key_value_list);
2894
2895
2896 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_id()-');
2897
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ct_id()');
2901
2902 RAISE;
2903
2904 END;
2905
2906 /*===========================================================================+
2907 | PROCEDURE |
2908 | delete_f_ctl_id |
2909 | |
2910 | DESCRIPTION |
2911 | This procedure deletes the ra_cust_trx_line_gl_dist rows identified |
2912 | by the p_customer_trx_line_id parameter. |
2913 | |
2914 | SCOPE - PUBLIC |
2915 | |
2916 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2917 | arp_util.debug |
2918 | |
2919 | ARGUMENTS : IN: |
2920 | p_customer_trx_line_id - identifies the rows to delete |
2921 | p_account_set_flag - value is used to restrict delete |
2922 | p_account_class - value is used to restrict delete |
2923 | OUT: |
2924 | None |
2925 | |
2926 | RETURNS : NONE |
2927 | |
2928 | NOTES |
2929 | |
2930 | MODIFICATION HISTORY |
2931 | 09-JUN-95 Charlie Tomberg Created |
2932 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2933 | added processing calls for delete from |
2934 | ra_cust_trx_lines_gl_dist |
2935 | |
2936 +===========================================================================*/
2937
2938 procedure delete_f_ctl_id( p_customer_trx_line_id
2939 IN ra_customer_trx_lines.customer_trx_line_id%type,
2940 p_account_set_flag
2941 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
2942 p_account_class
2943 IN ra_cust_trx_line_gl_dist.account_class%type)
2944 IS
2945
2946 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2947 l_amt NUMBER := 0;
2948 l_acctd_amt NUMBER := 0;
2949
2950 BEGIN
2951
2952
2953 arp_util.debug('arp_ctlgd_pkg.delete_f_ctl_id()+');
2954
2955 DELETE FROM ra_cust_trx_line_gl_dist
2956 WHERE customer_trx_line_id = p_customer_trx_line_id
2957 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
2958 AND account_class = nvl(p_account_class, account_class)
2959 RETURNING cust_trx_line_gl_dist_id
2960 BULK COLLECT INTO l_gl_dist_key_value_list;
2961
2962 BEGIN
2963 SELECT sum(nvl(amount,0)), sum(nvl(acctd_amount,0))
2964 INTO l_amt, l_acctd_amt
2965 FROM ra_cust_trx_line_gl_dist
2966 WHERE customer_trx_id = (SELECT customer_trx_id
2967 FROM ra_customer_trx_lines
2968 WHERE customer_trx_line_id = p_customer_trx_line_id)
2969 AND account_class <> 'REC'
2970 AND account_set_flag = 'N';
2971 EXCEPTION
2972 WHEN OTHERS THEN
2973 l_amt := 0;
2974 l_acctd_amt := 0;
2975 END;
2976
2977 UPDATE ra_cust_trx_line_gl_dist
2978 SET amount = nvl(l_amt,0),
2979 acctd_amount = nvl(l_acctd_amt,0),
2980 last_updated_by = pg_user_id,
2981 last_update_date = sysdate,
2982 last_update_login = pg_login_id
2983 WHERE customer_trx_id = (SELECT customer_trx_id
2984 FROM ra_customer_trx_lines
2985 WHERE customer_trx_line_id = p_customer_trx_line_id)
2986 and account_class = 'REC'
2987 and account_set_flag = 'N';
2988
2989 arp_standard.debug('calling mrc engine for insertion of gl dist data');
2990 ar_mrc_engine.maintain_mrc_data(
2991 p_event_mode => 'DELETE',
2992 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2993 p_mode => 'BATCH',
2994 p_key_value_list => l_gl_dist_key_value_list);
2995
2996 arp_util.debug('arp_ctlgd_pkg.delete_f_ctl_id()-');
2997
2998 EXCEPTION
2999 WHEN OTHERS THEN
3000 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ctl_id()');
3001
3002 RAISE;
3003
3004 END;
3005
3006 /*===========================================================================+
3007 | PROCEDURE |
3008 | delete_f_ctls_id |
3009 | |
3010 | DESCRIPTION |
3011 | This procedure deletes the ra_cust_trx_line_gl_dist rows identified |
3012 | by the p_cust_trx_line_salesrep_id parameter. |
3013 | |
3014 | SCOPE - PUBLIC |
3015 | |
3016 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3017 | arp_util.debug |
3018 | |
3019 | ARGUMENTS : IN: |
3020 | p_cust_trx_line_salesrep - identifies the rows to delete |
3021 | p_account_set_flag - value is used to restrict delete |
3022 | p_account_class - value is used to restrict delete |
3023 | OUT: |
3024 | None |
3025 | |
3026 | RETURNS : NONE |
3027 | |
3028 | NOTES |
3029 | |
3030 | MODIFICATION HISTORY |
3031 | 09-JUN-95 Charlie Tomberg Created |
3032 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3033 | added processing calls for delete from |
3034 | ra_cust_trx_lines_gl_dist |
3035 | |
3036 +===========================================================================*/
3037
3038 procedure delete_f_ctls_id( p_cust_trx_line_salesrep_id
3039 IN ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type,
3040 p_account_set_flag
3041 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3042 p_account_class
3043 IN ra_cust_trx_line_gl_dist.account_class%type)
3044 IS
3045
3046 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3047
3048 BEGIN
3049
3050 arp_util.debug('arp_ctlgd_pkg.delete_f_ctls_id()+');
3051
3052 DELETE FROM ra_cust_trx_line_gl_dist
3053 WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
3054 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
3055 AND account_class = nvl(p_account_class, account_class)
3056 RETURNING cust_trx_line_gl_dist_id
3057 BULK COLLECT INTO l_gl_dist_key_value_list;
3058
3059 arp_standard.debug('calling mrc engine for insertion of gl dist data');
3060 ar_mrc_engine.maintain_mrc_data(
3061 p_event_mode => 'DELETE',
3062 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3063 p_mode => 'BATCH',
3064 p_key_value_list => l_gl_dist_key_value_list);
3065
3066
3067 arp_util.debug('arp_ctlgd_pkg.delete_f_ctls_id()-');
3068
3069 EXCEPTION
3070 WHEN OTHERS THEN
3071 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ctls_id()');
3072
3073 RAISE;
3074
3075 END;
3076
3077 /*===========================================================================+
3078 | PROCEDURE |
3079 | delete_f_ct_ltctl_id_type |
3080 | |
3081 | DESCRIPTION |
3082 | This procedure deletes the ra_cust_trx_line_gl_dist rows corresponding |
3083 | to the child lines (incl. header freight line) identified by |
3084 | p_customer_trx_id, p_link_to_cust_trx_line_id and p_line_type |
3085 | |
3086 | SCOPE - PUBLIC |
3087 | |
3088 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3089 | arp_util.debug |
3090 | |
3091 | ARGUMENTS : IN: |
3092 | p_customer_trx_id - identifies the transaction |
3093 | p_link_to_cust_trx_line_id - identifies the parent line |
3094 | p_line_type - identifies the parent line |
3095 | type |
3096 | p_account_set_flag - value is used to restrict delete |
3097 | p_account_class - value is used to restrict delete |
3098 | OUT: |
3099 | None |
3100 | |
3101 | RETURNS : NONE |
3102 | |
3103 | NOTES |
3104 | |
3105 | MODIFICATION HISTORY |
3106 | 14-SEP-95 Subash Chadalavada Created |
3107 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3108 | added processing calls for delete from |
3109 | ra_cust_trx_lines_gl_dist |
3110 +===========================================================================*/
3111
3112 PROCEDURE delete_f_ct_ltctl_id_type(
3113 p_customer_trx_id IN
3114 ra_customer_trx.customer_trx_id%type,
3115 p_link_to_cust_trx_line_id IN
3116 ra_customer_trx_lines.link_to_cust_trx_line_id%type,
3117 p_line_type IN
3118 ra_customer_trx_lines.line_type%type,
3119 p_account_set_flag IN
3120 ra_cust_trx_line_gl_dist.account_set_flag%type,
3121 p_account_class IN
3122 ra_cust_trx_line_gl_dist.account_class%type)
3123 IS
3124 l_amt NUMBER := 0;
3125 l_acctd_amt NUMBER := 0;
3126
3127 BEGIN
3128
3129 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_ltctl_id_type()+');
3130
3131 DELETE FROM ra_cust_trx_line_gl_dist
3132 WHERE customer_trx_id = p_customer_trx_id
3133 AND customer_trx_line_id in
3134 ( SELECT customer_trx_line_id
3135 FROM ra_customer_trx_lines
3136 WHERE customer_trx_id = p_customer_trx_id
3137 AND decode(p_link_to_cust_trx_line_id,
3138 null, -99,
3139 link_to_cust_trx_line_id) =
3140 nvl(p_link_to_cust_trx_line_id, -99)
3141 AND line_type = nvl(p_line_type, line_type)
3142 )
3143 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
3144 AND account_class = nvl(p_account_class, account_class);
3145
3146 BEGIN
3147 SELECT sum(nvl(amount,0)), sum(nvl(acctd_amount,0))
3148 INTO l_amt, l_acctd_amt
3149 FROM ra_cust_trx_line_gl_dist
3150 WHERE customer_trx_id = p_customer_trx_id
3151 AND account_class <> 'REC'
3152 AND account_set_flag = 'N';
3153 EXCEPTION
3154 WHEN OTHERS THEN
3155 l_amt := 0;
3156 l_acctd_amt := 0;
3157 END;
3158
3159 UPDATE ra_cust_trx_line_gl_dist
3160 SET amount = nvl(l_amt,0),
3161 acctd_amount = nvl(l_acctd_amt,0),
3162 last_updated_by = pg_user_id,
3163 last_update_date = sysdate,
3164 last_update_login = pg_login_id
3165 WHERE customer_trx_id = p_customer_trx_id
3166 and account_class = 'REC'
3167 and account_set_flag = 'N';
3168
3169 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_ltctl_id_type()-');
3170
3171 EXCEPTION
3172 WHEN OTHERS THEN
3173 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ct_ltctl_id_type()');
3174
3175 RAISE;
3176
3177 END;
3178
3179
3180 /*===========================================================================+
3181 | PROCEDURE |
3182 | update_p |
3183 | |
3184 | DESCRIPTION |
3185 | This procedure updates the ra_cust_trx_line_gl_dist row identified |
3186 | by the p_cust_trx_line_gl_dist_id parameter. |
3187 | |
3188 | SCOPE - PUBLIC |
3189 | |
3190 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3191 | arp_util.debug |
3192 | |
3193 | ARGUMENTS : IN: |
3194 | p_cust_trx_line_gl_dist_id - identifies the row to update |
3195 | p_dist_rec - contains the new column values |
3196 | p_exchange_rate |
3197 | p_currency_code |
3198 | p_precision |
3199 | p_mau |
3200 | OUT: |
3201 | None |
3202 | |
3203 | RETURNS : NONE |
3204 | |
3205 | NOTES |
3206 | set_to_dummy must be called before the values in p_dist_rec are |
3207 | changed and this function is called. |
3208 | |
3209 | MODIFICATION HISTORY |
3210 | 09-JUN-95 Charlie Tomberg Created |
3211 | |
3212 +===========================================================================*/
3213
3214 PROCEDURE update_p( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3215 p_cust_trx_line_gl_dist_id IN
3216 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
3217 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3218 DEFAULT 1,
3219 p_currency_code IN fnd_currencies.currency_code%type
3220 DEFAULT null,
3221 p_precision IN fnd_currencies.precision%type
3222 DEFAULT null,
3223 p_mau IN
3224 fnd_currencies.minimum_accountable_unit%type
3225 DEFAULT null)
3226 IS
3227
3228 l_where varchar2(500);
3229
3230 BEGIN
3231
3232 arp_util.debug('arp_ctlgd_pkg.update_p()+ ' ||
3233 to_char(sysdate, 'HH:MI:SS'));
3234
3235 l_where := ' WHERE cust_trx_line_gl_dist_id = :where_1 ' ||
3236 'AND account_set_flag = nvl(:where_account_set_flag, '||
3237 'account_set_flag) ' ||
3238 'AND account_class = nvl(:where_account_class, account_class)';
3239
3240
3241 arp_ctlgd_pkg.generic_update( pg_cursor1,
3242 l_where,
3243 p_cust_trx_line_gl_dist_id,
3244 null,
3245 null,
3246 p_exchange_rate,
3247 p_currency_code,
3248 p_precision,
3249 p_mau,
3250 p_dist_rec);
3251
3252 arp_util.debug('arp_ctlgd_pkg.update_p()- ' ||
3253 to_char(sysdate, 'HH:MI:SS'));
3254
3255
3256 EXCEPTION
3257 WHEN OTHERS THEN
3258 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_p()');
3259 RAISE;
3260 END;
3261
3262
3263 /*===========================================================================+
3264 | PROCEDURE |
3265 | update_f_ct_id |
3266 | |
3267 | DESCRIPTION |
3268 | This procedure updates the ra_cust_trx_line_gl_dist rows identified |
3269 | by the p_customer_trx_id parameter. |
3270 | |
3271 | SCOPE - PUBLIC |
3272 | |
3273 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3274 | arp_util.debug |
3275 | |
3276 | ARGUMENTS : IN: |
3277 | p_customer_trx_id - identifies the rows to update |
3278 | p_dist_rec - contains the new column values |
3279 | p_account_set_flag - value is used to restrict update |
3280 | p_account_class - value is used to restrict update |
3281 | p_exchange_rate |
3282 | p_currency_code |
3283 | p_precision |
3284 | p_mau |
3285 | OUT: |
3286 | None |
3287 | |
3288 | RETURNS : NONE |
3289 | |
3290 | NOTES |
3291 | set_to_dummy must be called before the values in p_dist_rec are |
3292 | changed and this function is called. |
3293 | |
3294 | MODIFICATION HISTORY |
3295 | 09-JUN-95 Charlie Tomberg Created |
3296 | |
3297 +===========================================================================*/
3298
3299 PROCEDURE update_f_ct_id( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3300 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
3301 p_account_set_flag
3302 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3303 p_account_class
3304 IN ra_cust_trx_line_gl_dist.account_class%type,
3305 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3306 DEFAULT 1,
3307 p_currency_code IN fnd_currencies.currency_code%type
3308 DEFAULT null,
3309 p_precision IN fnd_currencies.precision%type
3310 DEFAULT null,
3311 p_mau IN
3312 fnd_currencies.minimum_accountable_unit%type
3313 DEFAULT null)
3314 IS
3315
3316 l_where varchar2(500);
3317
3318 BEGIN
3319
3320 arp_util.debug('arp_ctlgd_pkg.update_f_ct_id()+ ' ||
3321 to_char(sysdate, 'HH:MI:SS'));
3322
3323 l_where := ' WHERE customer_trx_id = :where_1 ' ||
3324 'AND account_set_flag = nvl(:where_account_set_flag, '||
3325 'account_set_flag) ' ||
3326 'AND account_class = nvl(:where_account_class, account_class)';
3327
3328 arp_ctlgd_pkg.generic_update( pg_cursor2,
3329 l_where,
3330 p_customer_trx_id,
3331 p_account_set_flag,
3332 p_account_class,
3333 p_exchange_rate,
3334 p_currency_code,
3335 p_precision,
3336 p_mau,
3337 p_dist_rec);
3338
3339 arp_util.debug('arp_ctlgd_pkg.update_f_ct_id()- ' ||
3340 to_char(sysdate, 'HH:MI:SS'));
3341
3342
3343 EXCEPTION
3344 WHEN OTHERS THEN
3345 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_f_ct_id()');
3346 RAISE;
3347 END;
3348
3349
3350 /*===========================================================================+
3351 | PROCEDURE |
3352 | update_f_ctl_id |
3353 | |
3354 | DESCRIPTION |
3355 | This procedure updates the ra_cust_trx_line_gl_dist rows identified |
3356 | by the p_customer_trx_line_id parameter. |
3357 | |
3358 | SCOPE - PUBLIC |
3359 | |
3360 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3361 | arp_util.debug |
3362 | |
3363 | ARGUMENTS : IN: |
3364 | p_customer_trx_line_id - identifies the rows to update |
3365 | p_dist_rec - contains the new column values |
3366 | p_account_set_flag - value is used to restrict update |
3367 | p_account_class - value is used to restrict update |
3368 | p_exchange_rate |
3369 | p_currency_code |
3370 | p_precision |
3371 | p_mau |
3372 | OUT: |
3373 | None |
3374 | |
3375 | RETURNS : NONE |
3376 | |
3377 | NOTES |
3378 | set_to_dummy must be called before the values in p_dist_rec are |
3379 | changed and this function is called. |
3380 | |
3381 | MODIFICATION HISTORY |
3382 | 09-JUN-95 Charlie Tomberg Created |
3383 | |
3384 +===========================================================================*/
3385
3386 PROCEDURE update_f_ctl_id( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3387 p_customer_trx_line_id IN
3388 ra_customer_trx_lines.customer_trx_line_id%type,
3389 p_account_set_flag
3390 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3391 p_account_class
3392 IN ra_cust_trx_line_gl_dist.account_class%type,
3393 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3394 DEFAULT 1,
3395 p_currency_code IN fnd_currencies.currency_code%type
3396 DEFAULT null,
3397 p_precision IN fnd_currencies.precision%type
3398 DEFAULT null,
3399 p_mau IN
3400 fnd_currencies.minimum_accountable_unit%type
3401 DEFAULT null)
3402 IS
3403
3404 l_where varchar2(500);
3405
3406 BEGIN
3407
3408 arp_util.debug('arp_ctlgd_pkg.update_f_ctl_id()+ ' ||
3409 to_char(sysdate, 'HH:MI:SS'));
3410
3411 l_where := ' WHERE customer_trx_line_id = :where_1 ' ||
3412 'AND account_set_flag = nvl(:where_account_set_flag, '||
3413 'account_set_flag) ' ||
3414 'AND account_class = nvl(:where_account_class, account_class)';
3415
3416
3417 arp_ctlgd_pkg.generic_update( pg_cursor3,
3418 l_where,
3419 p_customer_trx_line_id,
3420 p_account_set_flag,
3421 p_account_class,
3422 p_exchange_rate,
3423 p_currency_code,
3424 p_precision,
3425 p_mau,
3426 p_dist_rec);
3427
3428 arp_util.debug('arp_ctlgd_pkg.update_f_ctl_id()- ' ||
3429 to_char(sysdate, 'HH:MI:SS'));
3430
3431
3432 EXCEPTION
3433 WHEN OTHERS THEN
3434 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_f_ctl_id()');
3435 RAISE;
3436 END;
3437
3438 /*===========================================================================+
3439 | PROCEDURE |
3440 | update_f_ctls_id |
3441 | |
3442 | DESCRIPTION |
3443 | This procedure updates the ra_cust_trx_line_gl_dist rows identified |
3444 | by the p_cust_trx_line_salesrep_id parameter. |
3445 | |
3446 | SCOPE - PUBLIC |
3447 | |
3448 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3449 | arp_util.debug |
3450 | |
3451 | ARGUMENTS : IN: |
3452 | p_cust_trx_line_salesrep_id - identifies the rows to update |
3453 | p_dist_rec - contains the new column values |
3454 | p_account_set_flag - value is used to restrict update |
3455 | p_account_class - value is used to restrict update |
3456 | p_exchange_rate |
3457 | p_currency_code |
3458 | p_precision |
3459 | p_mau |
3460 | OUT: |
3461 | None |
3462 | |
3463 | RETURNS : NONE |
3464 | |
3465 | NOTES |
3466 | set_to_dummy must be called before the values in p_dist_rec are |
3467 | changed and this function is called. |
3468 | |
3469 | MODIFICATION HISTORY |
3470 | 09-JUN-95 Charlie Tomberg Created |
3471 | |
3472 +===========================================================================*/
3473
3474 PROCEDURE update_f_ctls_id( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3475 p_cust_trx_line_salesrep_id IN
3476 ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type,
3477 p_account_set_flag
3478 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3479 p_account_class
3480 IN ra_cust_trx_line_gl_dist.account_class%type,
3481 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3482 DEFAULT 1,
3483 p_currency_code IN fnd_currencies.currency_code%type
3484 DEFAULT null,
3485 p_precision IN fnd_currencies.precision%type
3486 DEFAULT null,
3487 p_mau IN
3488 fnd_currencies.minimum_accountable_unit%type
3489 DEFAULT null)
3490 IS
3491
3492 l_where varchar2(500);
3493
3494 BEGIN
3495
3496 arp_util.debug('arp_ctlgd_pkg.update_f_ctls_id()+ ' ||
3497 to_char(sysdate, 'HH:MI:SS'));
3498
3499 l_where := ' WHERE cust_trx_line_salesrep_id = :where_1 ' ||
3500 'AND account_set_flag = nvl(:where_account_set_flag, '||
3501 'account_set_flag) ' ||
3502 'AND account_class = nvl(:where_account_class, account_class)';
3503
3504 arp_ctlgd_pkg.generic_update( pg_cursor4,
3505 l_where,
3506 p_cust_trx_line_salesrep_id,
3507 p_account_set_flag,
3508 p_account_class,
3509 p_exchange_rate,
3510 p_currency_code,
3511 p_precision,
3512 p_mau,
3513 p_dist_rec);
3514
3515 arp_util.debug('arp_ctlgd_pkg.update_f_ctls_id()- ' ||
3516 to_char(sysdate, 'HH:MI:SS'));
3517
3518
3519 EXCEPTION
3520 WHEN OTHERS THEN
3521 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_f_ctls_id()');
3522 RAISE;
3523 END;
3524
3525
3526 /*===========================================================================+
3527 | PROCEDURE |
3528 | update_acctd_amount |
3529 | |
3530 | DESCRIPTION |
3531 | |
3532 | Updates ra_cust_trx_line_gl_dist.acctd_amount for rows identified by |
3533 | the p_customer_trx_id parameter. |
3534 | |
3535 | SCOPE - PUBLIC |
3536 | |
3537 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3538 | arp_util.debug |
3539 | |
3540 | ARGUMENTS : IN: |
3541 | p_customer_trx_id - required |
3542 | p_base_curr_code - required |
3543 | p_exchange_rate - required |
3544 | p_base_precision - optional |
3545 | p_base_min_acc_unit - optional |
3546 | OUT: |
3547 | RETURNS : NONE |
3548 | |
3549 | NOTES |
3550 | If p_base_precision and p_base_min_acc_unit are null, |
3551 | arpcurr.functional_amount figures out NOCOPY what they are. |
3552 | |
3553 | MODIFICATION HISTORY |
3554 | 21-JUL-95 Martin Johnson Created |
3555 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3556 | added calls for processing updates to |
3557 | ra_cust_trx_line_gl_dist. |
3558 | 31-AUG-04 M Raymond Added IF condition to only do MRC
3559 | update if rows were updated in primary
3560 +===========================================================================*/
3561
3562 PROCEDURE update_acctd_amount(p_customer_trx_id IN number,
3563 p_base_curr_code IN
3564 fnd_currencies.currency_code%type,
3565 p_exchange_rate IN
3566 ra_customer_trx.exchange_rate%type,
3567 p_base_precision IN
3568 fnd_currencies.precision%type
3569 default null,
3570 p_base_min_acc_unit IN
3571 fnd_currencies.minimum_accountable_unit%type
3572 default null)
3573 IS
3574 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3575
3576 BEGIN
3577
3578 arp_util.debug('arp_ctlgd_pkg.update_acctd_amount()+');
3579
3580 update ra_cust_trx_line_gl_dist
3581 set last_updated_by = pg_user_id,
3582 last_update_date = sysdate,
3583 last_update_login = pg_login_id,
3584 acctd_amount = arpcurr.functional_amount(
3585 amount,
3586 p_base_curr_code,
3587 p_exchange_rate,
3588 p_base_precision,
3589 p_base_min_acc_unit)
3590 where customer_trx_id = p_customer_trx_id
3591 and account_set_flag = 'N'
3592 and account_class <> 'REC';
3593
3594 /* Bug 3858542 - Added IF condition to prevent failures in
3595 MRC code when no rows will be updated */
3596 IF (SQL%ROWCOUNT > 0 )
3597 THEN
3598 arp_standard.debug('calling mrc engine for update of gl dist data');
3599 ar_mrc_engine.maintain_mrc_data(
3600 p_event_mode => 'UPDATE',
3601 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3602 p_mode => 'BATCH',
3603 p_key_value_list => l_gl_dist_key_value_list);
3604 END IF;
3605
3606 arp_util.debug('arp_ctlgd_pkg.update_acctd_amount()-');
3607
3608 EXCEPTION
3609 WHEN NO_DATA_FOUND THEN
3610 arp_util.debug('arp_ctlgd_pkg.update_acctd_amount()-');
3611 WHEN OTHERS THEN
3612 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_acctd_amount()');
3613 RAISE;
3614 END;
3615
3616 /*===========================================================================+
3617 | PROCEDURE |
3618 | update_amount_f_ctl_id |
3619 | |
3620 | DESCRIPTION |
3621 | |
3622 | Updates ra_cust_trx_line_gl_dist amount columns for rows identified by |
3623 | the p_customer_trx_id parameter. |
3624 | |
3625 | SCOPE - PUBLIC |
3626 | |
3627 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3628 | arp_util.debug |
3629 | |
3630 | ARGUMENTS : IN: |
3631 | p_customer_trx_id - required |
3632 | p_line_amount - required |
3633 | p_base_curr_code - required |
3634 | p_exchange_rate - required |
3635 | p_base_precision - optional |
3636 | p_base_min_acc_unit - optional |
3637 | p_foreign_currency - required |
3638 | OUT: |
3639 | RETURNS : NONE |
3640 | |
3641 | NOTES |
3642 | If p_base_precision and p_base_min_acc_unit are null, |
3643 | arpcurr.functional_amount figures out NOCOPY what they are. |
3644 | |
3645 | MODIFICATION HISTORY |
3646 | 24-JUL-95 Martin Johnson Created |
3647 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3648 | added calls for processing updates to |
3649 | ra_cust_trx_line_gl_dist. |
3650 | |
3651 +===========================================================================*/
3652
3653 PROCEDURE update_amount_f_ctl_id(p_customer_trx_line_id IN
3654 ra_customer_trx_lines.customer_trx_line_id%type,
3655 p_line_amount IN
3656 ra_customer_trx_lines.extended_amount%type,
3657 p_foreign_currency_code IN
3658 fnd_currencies.currency_code%type,
3659 p_base_curr_code IN
3660 fnd_currencies.currency_code%type,
3661 p_exchange_rate IN
3662 ra_customer_trx.exchange_rate%type,
3663 p_base_precision IN
3664 fnd_currencies.precision%type
3665 default null,
3666 p_base_min_acc_unit IN
3667 fnd_currencies.minimum_accountable_unit%type
3668 default null)
3669 IS
3670 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3671
3672 BEGIN
3673
3674 arp_util.debug('arp_ctlgd_pkg.update_amount_f_ctl_id()+');
3675
3676 UPDATE ra_cust_trx_line_gl_dist
3677 SET last_updated_by = pg_user_id,
3678 last_update_date = sysdate,
3679 last_update_login = pg_login_id,
3680 amount = arpcurr.CurrRound(
3681 p_line_amount *
3682 ( percent / 100 ),
3683 p_foreign_currency_code
3684 ),
3685 acctd_amount = arpcurr.functional_amount(
3686 arpcurr.CurrRound(
3687 p_line_amount *
3688 ( percent / 100 ),
3689 p_foreign_currency_code
3690 ),
3691 p_base_curr_code,
3692 p_exchange_rate,
3693 p_base_precision,
3694 p_base_min_acc_unit)
3695 WHERE customer_trx_line_id = p_customer_trx_line_id
3696 AND account_set_flag = 'N'
3697 RETURNING cust_trx_line_gl_dist_id
3698 BULK COLLECT INTO l_gl_dist_key_value_list;
3699 IF (SQL%ROWCOUNT > 0 ) then
3700 /* Used arp_global.request_id for bug3620556 */
3701 IF arp_global.request_id is NULL then
3702 arp_standard.debug('calling mrc engine for update of gl dist data');
3703 ar_mrc_engine.maintain_mrc_data(
3704 p_event_mode => 'UPDATE',
3705 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3706 p_mode => 'BATCH',
3707 p_key_value_list => l_gl_dist_key_value_list);
3708 END IF;
3709 END IF;
3710 arp_util.debug('arp_ctlgd_pkg.update_amount_f_ctl_id()-');
3711
3712 EXCEPTION
3713 WHEN NO_DATA_FOUND THEN
3714 arp_util.debug('arp_ctlgd_pkg.update_amount_f_ctl_id()-');
3715 WHEN OTHERS THEN
3716 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_amount_f_ctl_id()');
3717 RAISE;
3718 END;
3719
3720
3721 /*===========================================================================+
3722 | PROCEDURE |
3723 | insert_p |
3724 | |
3725 | DESCRIPTION |
3726 | This procedure inserts a row into ra_cust_trx_line_gl_dists that |
3727 | contains the column values specified in the p_dist_rec parameter. |
3728 | |
3729 | SCOPE - PUBLIC |
3730 | |
3731 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3732 | arpcurr.functional_amount |
3733 | arp_util.debug |
3734 | arp_global.set_of_books_id |
3735 | |
3736 | ARGUMENTS : IN: |
3737 | p_dist_rec - contains the new column values |
3738 | OUT: |
3739 | p_cust_trx_line_gl_dist_id - unique ID of the new row |
3740 | p_exchange_rate |
3741 | p_currency_code |
3742 | p_precision |
3743 | p_mau |
3744 | |
3745 | RETURNS : NONE |
3746 | |
3747 | NOTES |
3748 | |
3749 | MODIFICATION HISTORY |
3750 | 09-JUN-95 Charlie Tomberg Created |
3751 | 10-OCT-95 Martin Johnson Always populate posting_control_id |
3752 | with -3. |
3753 | Populate latest_rec_flag with 'Y' |
3754 | if account_class is REC, otherwise |
3755 | populate with null. |
3756 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3757 | added calls for processing inserts to |
3758 | ra_cust_trx_line_gl_dist. |
3759 | |
3760 +===========================================================================*/
3761
3762 PROCEDURE insert_p(
3763 p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3764 p_cust_trx_line_gl_dist_id
3765 OUT NOCOPY ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
3766 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3767 DEFAULT 1,
3768 p_currency_code IN fnd_currencies.currency_code%type
3769 DEFAULT null,
3770 p_precision IN fnd_currencies.precision%type
3771 DEFAULT null,
3772 p_mau IN fnd_currencies.minimum_accountable_unit%type
3773 DEFAULT null
3774 ) IS
3775
3776
3777 l_cust_trx_line_gl_dist_id
3778 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
3779
3780
3781 BEGIN
3782
3783 arp_util.debug('arp_ctlgd_pkg.insert_p()+');
3784
3785 p_cust_trx_line_gl_dist_id := '';
3786
3787 /*---------------------------*
3788 | Get the unique identifier |
3789 *---------------------------*/
3790
3791 SELECT RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL
3792 INTO l_cust_trx_line_gl_dist_id
3793 FROM DUAL;
3794
3795
3796 /*-------------------*
3797 | Insert the record |
3798 *-------------------*/
3799
3800 INSERT INTO ra_cust_trx_line_gl_dist
3801 (
3802 cust_trx_line_gl_dist_id,
3803 customer_trx_id,
3804 customer_trx_line_id,
3805 cust_trx_line_salesrep_id,
3806 account_class,
3807 percent,
3808 amount,
3809 acctd_amount,
3810 gl_date,
3811 original_gl_date,
3812 gl_posted_date,
3813 code_combination_id,
3814 concatenated_segments,
3815 collected_tax_ccid,
3816 collected_tax_concat_seg,
3817 comments,
3818 account_set_flag,
3819 latest_rec_flag,
3820 rec_offset_flag,
3821 ussgl_transaction_code,
3822 ussgl_transaction_code_context,
3823 attribute_category,
3824 attribute1,
3825 attribute2,
3826 attribute3,
3827 attribute4,
3828 attribute5,
3829 attribute6,
3830 attribute7,
3831 attribute8,
3832 attribute9,
3833 attribute10,
3834 attribute11,
3835 attribute12,
3836 attribute13,
3837 attribute14,
3838 attribute15,
3839 set_of_books_id,
3840 posting_control_id,
3841 last_update_date,
3842 last_updated_by,
3843 creation_date,
3844 created_by,
3845 last_update_login,
3846 program_application_id,
3847 program_id,
3848 program_update_date,
3849 org_id,
3850 rounding_correction_flag,
3851 ccid_change_flag /* Bug 8788491 */
3852 )
3853 VALUES
3854 (
3855 l_cust_trx_line_gl_dist_id,
3856 p_dist_rec.customer_trx_id,
3857 p_dist_rec.customer_trx_line_id,
3858 p_dist_rec.cust_trx_line_salesrep_id,
3859 p_dist_rec.account_class,
3860 p_dist_rec.percent,
3861 p_dist_rec.amount,
3862 nvl(
3863 p_dist_rec.acctd_amount,
3864 decode(p_dist_rec.amount,
3865 null, to_number(null),
3866 arpcurr.functional_amount(
3867 p_dist_rec.amount,
3868 p_currency_code,
3869 p_exchange_rate,
3870 p_precision,
3871 p_mau
3872 )
3873 )
3874 ),
3875 p_dist_rec.gl_date,
3876 p_dist_rec.original_gl_date,
3877 p_dist_rec.gl_posted_date,
3878 p_dist_rec.code_combination_id,
3879 p_dist_rec.concatenated_segments,
3880 p_dist_rec.collected_tax_ccid,
3881 p_dist_rec.collected_tax_concat_seg,
3882 p_dist_rec.comments,
3883 p_dist_rec.account_set_flag,
3884 decode(p_dist_rec.account_class,
3885 'REC', 'Y',
3886 null),
3887 p_dist_rec.rec_offset_flag,
3888 p_dist_rec.ussgl_transaction_code,
3889 p_dist_rec.ussgl_transaction_code_context,
3890 p_dist_rec.attribute_category,
3891 p_dist_rec.attribute1,
3892 p_dist_rec.attribute2,
3893 p_dist_rec.attribute3,
3894 p_dist_rec.attribute4,
3895 p_dist_rec.attribute5,
3896 p_dist_rec.attribute6,
3897 p_dist_rec.attribute7,
3898 p_dist_rec.attribute8,
3899 p_dist_rec.attribute9,
3900 p_dist_rec.attribute10,
3901 p_dist_rec.attribute11,
3902 p_dist_rec.attribute12,
3903 p_dist_rec.attribute13,
3904 p_dist_rec.attribute14,
3905 p_dist_rec.attribute15,
3906 arp_global.set_of_books_id, /* set_of_books_id */
3907 -3, /* posting_control_id */
3908 sysdate, /*last_update_date */
3909 pg_user_id, /* last_updated_by */
3910 sysdate, /* creation_date */
3911 pg_user_id, /* created_by */
3912 nvl(pg_conc_login_id,
3913 pg_login_id), /* last_update_login */
3914 pg_prog_appl_id, /* program_application_id */
3915 pg_conc_program_id, /* program_id */
3916 sysdate, /* program_update_date */
3917 arp_standard.sysparm.org_id, /* SSA changes anuj */
3918 p_dist_rec.rounding_correction_flag,
3919 p_dist_rec.ccid_change_flag /* Bug 8788491 */
3920 );
3921
3922 p_cust_trx_line_gl_dist_id := l_cust_trx_line_gl_dist_id;
3923
3924 arp_standard.debug('calling mrc engine for INSERT of gl dist data');
3925 ar_mrc_engine.maintain_mrc_data(
3926 p_event_mode => 'INSERT',
3927 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3928 p_mode => 'SINGLE',
3929 p_key_value => p_cust_trx_line_gl_dist_id);
3930
3931 arp_util.debug('arp_ctlgd_pkg.insert_p()-');
3932
3933 EXCEPTION
3934 WHEN OTHERS THEN
3935 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.insert_p()');
3936 RAISE;
3937 END;
3938
3939 /*===========================================================================+
3940 | PROCEDURE |
3941 | select_summary |
3942 | |
3943 | DESCRIPTION |
3944 | Selects the total revenue percent and amount for a given transaction |
3945 | or line. This procedure is used by the forms running totals mechanism. |
3946 | |
3947 | SCOPE - PUBLIC |
3948 | |
3949 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3950 | arp_util.debug |
3951 | |
3952 | ARGUMENTS : IN: |
3953 | p_customer_trx_id |
3954 | p_customer_trx_line_id |
3955 | p_cust_trx_line_salesrep_id |
3956 | p_mode - the code of the alt. region displayed |
3957 | p_account_set_flag |
3958 | OUT: |
3959 | None |
3960 | IN/ OUT: |
3961 | p_amt_total |
3962 | p_amt_total_rtot_db |
3963 | p_pct_total |
3964 | p_pct_total_rtot_db |
3965 | p_pct_rev_total |
3966 | p_pct_rev_total_rtot_db |
3967 | p_pct_offset_total |
3968 | p_pct_offset_total_rtot_db |
3969 | p_pct_suspense_total |
3970 | p_pct_suspense_total_rtot_db |
3971 | |
3972 | RETURNS : NONE |
3973 | |
3974 | NOTES |
3975 | |
3976 | MODIFICATION HISTORY |
3977 | 17-OCT-95 Martin Johnson Created |
3978 | 20-OCT-95 Martin Johnson Added parameters: |
3979 | p_account_set_flag |
3980 | p_pct_rev_total |
3981 | p_pct_rev_total_rtot_db |
3982 | p_pct_offset_total |
3983 | p_pct_offset_total_rtot_db |
3984 | p_pct_suspense_total |
3985 | p_pct_suspense_total_rtot_db |
3986 | |
3987 +===========================================================================*/
3988
3989 PROCEDURE select_summary(
3990 p_customer_trx_id IN number,
3991 p_customer_trx_line_id IN number,
3992 p_cust_trx_line_salesrep_id IN number,
3993 p_mode IN varchar2,
3994 p_account_set_flag IN varchar2,
3995 p_amt_total IN OUT NOCOPY number,
3996 p_amt_total_rtot_db IN OUT NOCOPY number,
3997 p_pct_total IN OUT NOCOPY number,
3998 p_pct_total_rtot_db IN OUT NOCOPY number,
3999 p_pct_rev_total IN OUT NOCOPY number,
4000 p_pct_rev_total_rtot_db IN OUT NOCOPY number,
4001 p_pct_offset_total IN OUT NOCOPY number,
4002 p_pct_offset_total_rtot_db IN OUT NOCOPY number,
4003 p_pct_suspense_total IN OUT NOCOPY number,
4004 p_pct_suspense_total_rtot_db IN OUT NOCOPY number ) IS
4005
4006 BEGIN
4007
4008 IF PG_DEBUG in ('Y', 'C') THEN
4009 arp_util.debug('arp_ctlgd_pkg.select_summary()+');
4010 END IF;
4011
4012 SELECT NVL( SUM( NVL(amount, 0) ), 0),
4013 NVL( SUM( NVL(amount, 0) ), 0),
4014 NVL( SUM( NVL(percent, 0) ), 0),
4015 NVL( SUM( NVL(percent, 0) ), 0),
4016 NVL( SUM( DECODE(account_class,
4017 'REV', NVL(percent, 0),
4018 0) ), 0),
4019 NVL( SUM( DECODE(account_class,
4020 'REV', NVL(percent, 0),
4021 0) ), 0),
4022 NVL( SUM( DECODE(account_class,
4023 'UNEARN', NVL(percent, 0),
4024 'UNBILL', NVL(percent, 0),
4025 0) ), 0),
4026 NVL( SUM( DECODE(account_class,
4027 'UNEARN', NVL(percent, 0),
4028 'UNBILL', NVL(percent, 0),
4029 0) ), 0),
4030 NVL( SUM( DECODE(account_class,
4031 'SUSPENSE', NVL(percent, 0),
4032 0) ), 0),
4033 NVL( SUM( DECODE(account_class,
4034 'SUSPENSE', NVL(percent, 0),
4035 0) ), 0)
4036 INTO p_amt_total,
4037 p_amt_total_rtot_db,
4038 p_pct_total,
4039 p_pct_total_rtot_db,
4040 p_pct_rev_total,
4041 p_pct_rev_total_rtot_db,
4042 p_pct_offset_total,
4043 p_pct_offset_total_rtot_db,
4044 p_pct_suspense_total,
4045 p_pct_suspense_total_rtot_db
4046 FROM ra_cust_trx_line_gl_dist
4047 WHERE customer_trx_id = p_customer_trx_id
4048 AND customer_trx_line_id =
4049 DECODE(p_mode,
4050 'TACC_ACC_ASSGN_SREP', customer_trx_line_id,
4051 'TACC_ACC_SETS_SREP', customer_trx_line_id,
4052 p_customer_trx_line_id )
4053 AND NVL( cust_trx_line_salesrep_id, -10) =
4054 DECODE(p_mode,
4055 'TACC_ACC_ASSGN_SREP', p_cust_trx_line_salesrep_id,
4056 'TACC_ACC_SETS_SREP', p_cust_trx_line_salesrep_id,
4057 NVL( cust_trx_line_salesrep_id, -10) )
4058 AND account_set_flag = p_account_set_flag;
4059
4060 IF PG_DEBUG in ('Y', 'C') THEN
4061 arp_util.debug('arp_ctlgd_pkg.select_summary()-');
4062 END IF;
4063
4064 EXCEPTION
4065 WHEN OTHERS THEN
4066 IF PG_DEBUG in ('Y', 'C') THEN
4067 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.select_summary()');
4068 END IF;
4069 RAISE;
4070
4071 END select_summary;
4072
4073 /*---------------------------------------------+
4074 | Package initialization section. |
4075 | Sets WHO column variables for later use. |
4076 +---------------------------------------------*/
4077
4078 BEGIN
4079
4080 pg_user_id := fnd_global.user_id;
4081 pg_conc_login_id := fnd_global.conc_login_id;
4082 pg_login_id := fnd_global.login_id;
4083 pg_prog_appl_id := fnd_global.prog_appl_id;
4084 pg_conc_program_id := fnd_global.conc_program_id;
4085
4086 pg_msg_level_debug := arp_global.MSG_LEVEL_DEBUG;
4087
4088 END ARP_CTLGD_PKG;