[Home] [Help]
PACKAGE BODY: APPS.ARP_CTLGD_PKG
Source
1 PACKAGE BODY ARP_CTLGD_PKG AS
2 /* $Header: ARTILGDB.pls 120.12.12000000.4 2007/10/23 20:08:30 mraymond 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
1033
1034 arp_util.debug('arp_ctlgd_pkg.bind_dist_variables()-');
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.bind_dist_variables()');
1039 RAISE;
1040
1041 END;
1042
1043
1044 /*===========================================================================+
1045 | PROCEDURE |
1046 | construct_dist_update_stmt |
1047 | |
1048 | DESCRIPTION |
1049 | Copies the text of the dynamic SQL update statement into the |
1050 | out NOCOPY paramater. The update statement does not contain a where clause |
1051 | since this is the dynamic part that is added later. |
1052 | |
1053 | SCOPE - PRIVATE |
1054 | |
1055 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1056 | arpcurr.functional_amount |
1057 | arp_util.debug |
1058 | |
1059 | ARGUMENTS : IN: |
1060 | None. |
1061 | OUT: |
1062 | update_text - text of the update statement |
1063 | |
1064 | RETURNS : NONE |
1065 | |
1066 | NOTES |
1067 | This statement only updates columns in the dist record that do not |
1068 | contain the dummy values that indicate that they should not be changed.|
1069 | |
1070 | MODIFICATION HISTORY |
1071 | 09-JUN-95 Charlie Tomberg Created |
1072 | |
1073 +===========================================================================*/
1074
1075 PROCEDURE construct_dist_update_stmt( update_text OUT NOCOPY varchar2) IS
1076
1077 BEGIN
1078 arp_util.debug('arp_ctlgd_pkg.construct_dist_update_stmt()+');
1079
1080 update_text :=
1081 'UPDATE ra_cust_trx_line_gl_dist
1082 SET cust_trx_line_gl_dist_id =
1083 DECODE(:cust_trx_line_gl_dist_id,
1084 :ar_number_dummy, cust_trx_line_gl_dist_id,
1085 :cust_trx_line_gl_dist_id),
1086 customer_trx_id =
1087 DECODE(:customer_trx_id,
1088 :ar_number_dummy, customer_trx_id,
1089 :customer_trx_id),
1090 customer_trx_line_id =
1091 DECODE(:customer_trx_line_id,
1092 :ar_number_dummy, customer_trx_line_id,
1093 :customer_trx_line_id),
1094 cust_trx_line_salesrep_id =
1095 DECODE(:cust_trx_line_salesrep_id,
1096 :ar_number_dummy, cust_trx_line_salesrep_id,
1097 :cust_trx_line_salesrep_id),
1098 account_class =
1099 DECODE(:account_class,
1100 :ar_text_dummy, account_class,
1101 :account_class),
1102 percent =
1103 DECODE(:percent,
1104 :ar_number_dummy, percent,
1105 :percent),
1106 amount =
1107 DECODE(:amount,
1108 :ar_number_dummy, amount,
1109 :amount),
1110 /* calculate the accounted amount only if
1111 - a new accounted amount has not ben passed in AND
1112 - the amount has changed. */
1113 acctd_amount =
1114 DECODE(:acctd_amount,
1115 :ar_number_dummy, decode(:amount,
1116 amount, acctd_amount,
1117 :ar_number_dummy, acctd_amount,
1118 null, null,
1119 arpcurr.functional_amount(
1120 :amount,
1121 :currency_code,
1122 :exchange_rate,
1123 :precision,
1124 :mau)
1125 ),
1126 :acctd_amount),
1127 gl_date =
1128 -- Only uodate the GL Date for Account Set = N
1129 -- and Receivable records.
1130 DECODE(
1131 DECODE(:account_set_flag,
1132 :ar_flag_dummy, account_set_flag,
1133 :account_set_flag) ||
1134 DECODE(:account_class,
1135 :ar_text_dummy, account_class,
1136 :account_class),
1137 ''YCHARGES'', TO_DATE(NULL),
1138 ''YFREIGHT'', TO_DATE(NULL),
1139 ''YREV'', TO_DATE(NULL),
1140 ''YSUSPENSE'', TO_DATE(NULL),
1141 ''YTAX'', TO_DATE(NULL),
1142 ''YUNBILL'', TO_DATE(NULL),
1143 ''YUNEARN'', TO_DATE(NULL),
1144 DECODE(:gl_date,
1145 :ar_date_dummy, gl_date,
1146 :gl_date)
1147 ),
1148 original_gl_date =
1149 -- Only uodate the GL Date for Account Set = N
1150 -- and Receivable records.
1151 DECODE(
1152 DECODE(:account_set_flag,
1153 :ar_flag_dummy, account_set_flag,
1154 :account_set_flag) ||
1155 DECODE(:account_class,
1156 :ar_text_dummy, account_class,
1157 :account_class),
1158 ''YCHARGES'', TO_DATE(NULL),
1159 ''YFREIGHT'', TO_DATE(NULL),
1160 ''YREV'', TO_DATE(NULL),
1161 ''YSUSPENSE'', TO_DATE(NULL),
1162 ''YTAX'', TO_DATE(NULL),
1163 ''YUNBILL'', TO_DATE(NULL),
1164 ''YUNEARN'', TO_DATE(NULL),
1165 DECODE(:original_gl_date,
1166 :ar_date_dummy,
1167 original_gl_date,
1168 :original_gl_date)
1169 ),
1170 gl_posted_date =
1171 DECODE(:gl_posted_date,
1172 :ar_date_dummy, gl_posted_date,
1173 :gl_posted_date),
1174 code_combination_id =
1175 DECODE(:code_combination_id,
1176 :ar_number_dummy, code_combination_id,
1177 :code_combination_id),
1178 concatenated_segments =
1179 DECODE(:concatenated_segments,
1180 :ar_text_dummy, concatenated_segments,
1181 :concatenated_segments),
1182 collected_tax_ccid =
1183 DECODE(:collected_tax_ccid,
1184 :ar_number_dummy, collected_tax_ccid,
1185 :collected_tax_ccid),
1186 collected_tax_concat_seg =
1187 DECODE(:collected_tax_concat_seg,
1188 :ar_text_dummy, collected_tax_concat_seg,
1189 :collected_tax_concat_seg),
1190 comments =
1191 DECODE(:comments,
1192 :ar_text_dummy, comments,
1193 :comments),
1194 account_set_flag =
1195 DECODE(:account_set_flag,
1196 :ar_flag_dummy, account_set_flag,
1197 :account_set_flag),
1198 latest_rec_flag =
1199 DECODE(:latest_rec_flag,
1200 :ar_flag_dummy, latest_rec_flag,
1201 :latest_rec_flag),
1202 rec_offset_flag =
1203 DECODE(:rec_offset_flag,
1204 :ar_flag_dummy, rec_offset_flag,
1205 :rec_offset_flag),
1206 rounding_correction_flag =
1207 DECODE(:rounding_correction_flag,
1208 :ar_flag_dummy, rounding_correction_flag,
1209 :rounding_correction_flag),
1210 ussgl_transaction_code =
1211 DECODE(:ussgl_transaction_code,
1212 :ar_text_dummy, ussgl_transaction_code,
1213 :ussgl_transaction_code),
1214 ussgl_transaction_code_context =
1215 DECODE(:ussgl_transaction_code_context,
1216 :ar_text_dummy, ussgl_transaction_code_context,
1217 :ussgl_transaction_code_context),
1218 attribute_category =
1219 DECODE(:attribute_category,
1220 :ar_text_dummy, attribute_category,
1221 :attribute_category),
1222 attribute1 =
1223 DECODE(:attribute1,
1224 :ar_text_dummy, attribute1,
1225 :attribute1),
1226 attribute2 =
1227 DECODE(:attribute2,
1228 :ar_text_dummy, attribute2,
1229 :attribute2),
1230 attribute3 =
1231 DECODE(:attribute3,
1232 :ar_text_dummy, attribute3,
1233 :attribute3),
1234 attribute4 =
1235 DECODE(:attribute4,
1236 :ar_text_dummy, attribute4,
1237 :attribute4),
1238 attribute5 =
1239 DECODE(:attribute5,
1240 :ar_text_dummy, attribute5,
1241 :attribute5),
1242 attribute6 =
1243 DECODE(:attribute6,
1244 :ar_text_dummy, attribute6,
1245 :attribute6),
1246 attribute7 =
1247 DECODE(:attribute7,
1248 :ar_text_dummy, attribute7,
1249 :attribute7),
1250 attribute8 =
1251 DECODE(:attribute8,
1252 :ar_text_dummy, attribute8,
1253 :attribute8),
1254 attribute9 =
1255 DECODE(:attribute9,
1256 :ar_text_dummy, attribute9,
1257 :attribute9),
1258 attribute10 =
1259 DECODE(:attribute10,
1260 :ar_text_dummy, attribute10,
1261 :attribute10),
1262 attribute11 =
1263 DECODE(:attribute11,
1264 :ar_text_dummy, attribute11,
1265 :attribute11),
1266 attribute12 =
1267 DECODE(:attribute12,
1268 :ar_text_dummy, attribute12,
1269 :attribute12),
1270 attribute13 =
1271 DECODE(:attribute13,
1272 :ar_text_dummy, attribute13,
1273 :attribute13),
1274 attribute14 =
1275 DECODE(:attribute14,
1276 :ar_text_dummy, attribute14,
1277 :attribute14),
1278 attribute15 =
1279 DECODE(:attribute15,
1280 :ar_text_dummy, attribute15,
1281 :attribute15),
1282 set_of_books_id =
1283 DECODE(:set_of_books_id,
1284 :ar_number_dummy, set_of_books_id,
1285 :set_of_books_id),
1286 posting_control_id =
1287 DECODE(:posting_control_id,
1288 :ar_number_dummy, posting_control_id,
1289 :posting_control_id),
1290 last_update_date =
1291 DECODE(:last_update_date,
1292 :ar_date_dummy, sysdate,
1293 :last_update_date),
1294 last_updated_by =
1295 DECODE(:last_updated_by,
1296 :ar_number_dummy, :pg_user_id,
1297 :last_updated_by),
1298 creation_date =
1299 DECODE(:creation_date,
1300 :ar_date_dummy, creation_date,
1301 :creation_date),
1302 created_by =
1303 DECODE(:created_by,
1304 :ar_number_dummy, created_by,
1305 :created_by),
1306 last_update_login =
1307 DECODE(:last_update_login,
1308 :ar_number_dummy, nvl(:pg_conc_login_id,
1309 :pg_login_id),
1310 :last_update_login),
1311 program_application_id =
1312 DECODE(:program_application_id,
1313 :ar_number_dummy, program_application_id,
1314 :program_application_id),
1315 program_id =
1316 DECODE(:program_id,
1317 :ar_number_dummy, program_id,
1318 :program_id),
1319 program_update_date =
1320 DECODE(:program_update_date,
1321 :ar_date_dummy, program_update_date,
1322 :program_update_date)';
1323
1324
1325 arp_util.debug('arp_ctlgd_pkg.construct_dist_update_stmt()-');
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.construct_dist_update_stmt()');
1330 RAISE;
1331
1332 END;
1333
1334 /*===========================================================================+
1335 | PROCEDURE |
1336 | generic_update |
1337 | |
1338 | DESCRIPTION |
1339 | This procedure Updates records in ra_cust_trx_line_gl_dist |
1340 | identified by the where clause that is passed in as a parameter. Only |
1341 | those columns in the dist record parameter that do not contain the |
1342 | special dummy values are updated. |
1343 | |
1344 | SCOPE - PRIVATE |
1345 | |
1346 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1347 | arp_util.debug |
1348 | dbms_sql.open_cursor |
1349 | dbms_sql.parse |
1350 | dbms_sql.execute |
1351 | dbms_sql.close_cursor |
1352 | |
1353 | ARGUMENTS : IN: |
1354 | p_update_cursor - identifies the cursor to use |
1355 | p_where_clause - identifies which rows to update |
1356 | p_where1 - value to bind into where clause |
1357 | p_account_set_flag - value is used to restrict update |
1358 | p_account_class - value is used to restrict update |
1359 | p_exchange_rate |
1360 | p_currency_code |
1361 | p_precision |
1362 | p_mau |
1363 | p_dist_rec - contains the new dist values |
1364 | OUT: |
1365 | None |
1366 | |
1367 | RETURNS : NONE |
1368 | |
1369 | NOTES |
1370 | |
1371 | MODIFICATION HISTORY |
1372 | 09-JUN-95 Charlie Tomberg Created |
1373 | |
1374 +===========================================================================*/
1375
1376 PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
1377 p_where_clause IN varchar2,
1378 p_where1 IN number,
1379 p_account_set_flag IN
1380 ra_cust_trx_line_gl_dist.account_set_flag%type,
1381 p_account_class IN
1382 ra_cust_trx_line_gl_dist.account_class%type,
1383 p_exchange_rate IN ra_customer_trx.exchange_rate%type,
1384 p_currency_code IN fnd_currencies.currency_code%type,
1385 p_precision IN fnd_currencies.precision%type,
1386 p_mau IN
1387 fnd_currencies.minimum_accountable_unit%type,
1388 p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype)
1389 IS
1390
1391 l_count number;
1392 l_update_statement varchar2(25000);
1393 gl_dist_array dbms_sql.number_table; /* mrc */
1394
1395 BEGIN
1396 arp_util.debug('arp_ctlgd_pkg.generic_update()+');
1397
1398 /*--------------------------------------------------------------+
1399 | If this update statement has not already been parsed, |
1400 | construct the statement and parse it. |
1401 | Otherwise, use the already parsed statement and rebind its |
1402 | variables. |
1403 +--------------------------------------------------------------*/
1404
1405 IF (p_update_cursor IS NULL)
1406 THEN
1407 p_update_cursor := dbms_sql.open_cursor;
1408
1409 /*---------------------------------+
1410 | Construct the update statement |
1411 +---------------------------------*/
1412
1413 arp_ctlgd_pkg.construct_dist_update_stmt(l_update_statement);
1414
1415 l_update_statement := l_update_statement || p_where_clause;
1416
1417 /*--------------------------------------------------------+
1418 | added on variables for bulk collect for mrc processing |
1419 +--------------------------------------------------------*/
1420
1421 l_update_statement := l_update_statement ||
1422 ' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
1423
1424 /*-----------------------------------------------+
1425 | Parse, bind, execute and close the statement |
1426 +-----------------------------------------------*/
1427
1428 dbms_sql.parse(p_update_cursor,
1429 l_update_statement,
1430 dbms_sql.v7);
1431
1432 END IF;
1433
1434 arp_ctlgd_pkg.bind_dist_variables(p_update_cursor,
1435 p_dist_rec,
1436 p_exchange_rate,
1437 p_currency_code,
1438 p_precision,
1439 p_mau);
1440
1441 /*-----------------------------------------+
1442 | Bind the variables in the where clause |
1443 +-----------------------------------------*/
1444
1445 dbms_sql.bind_variable(p_update_cursor, ':where_1',
1446 p_where1);
1447
1448 dbms_sql.bind_variable(p_update_cursor, ':where_account_set_flag',
1449 p_account_set_flag);
1450
1451 dbms_sql.bind_variable(p_update_cursor, ':where_account_class',
1452 p_account_class);
1453
1454 /*-----------------------+
1455 | bind output variable |
1456 +-----------------------*/
1457 dbms_sql.bind_array(p_update_cursor,':gl_dist_key_value',
1458 gl_dist_array);
1459
1460 l_count := dbms_sql.execute(p_update_cursor);
1461
1462 arp_util.debug( to_char(l_count) || ' rows updated');
1463
1464 /*------------------------------------------+
1465 | get RETURNING COLUMN into OUT NOCOPY bind array |
1466 +------------------------------------------*/
1467
1468 dbms_sql.variable_value( p_update_cursor, ':gl_dist_key_value',
1469 gl_dist_array);
1470
1471 /*------------------------------------------------------------+
1472 | Raise the NO_DATA_FOUND exception if no rows were updated |
1473 +------------------------------------------------------------*/
1474
1475 IF (l_count = 0)
1476 THEN RAISE NO_DATA_FOUND;
1477 END IF;
1478
1479 arp_standard.debug('before loop for MRC processing...');
1480 FOR I in gl_dist_array.FIRST .. gl_dist_array.LAST LOOP
1481 /*-----------------------------------------------------+
1482 | call mrc engine to update RA_CUST_TRX_LINES_GL_DIST |
1483 +-----------------------------------------------------*/
1484 arp_standard.debug('before calling maintain_mrc ');
1485 arp_standard.debug('gl dist array('||to_char(I) || ') = ' || to_char(gl_dist_array(I)));
1486
1487 ar_mrc_engine.maintain_mrc_data(
1488 p_event_mode => 'UPDATE',
1489 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
1490 p_mode => 'SINGLE',
1491 p_key_value => gl_dist_array(I));
1492 END LOOP;
1493
1494
1495
1496 arp_util.debug('arp_ctlgd_pkg.generic_update()-');
1497
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.generic_update()');
1501 arp_util.debug(l_update_statement);
1502 arp_util.debug('Error at character: ' ||
1503 to_char(dbms_sql.last_error_position));
1504 RAISE;
1505 END;
1506
1507 /*===========================================================================+
1508 | PROCEDURE |
1509 | set_to_dummy |
1510 | |
1511 | DESCRIPTION |
1512 | This procedure initializes all columns in the parameter dist record |
1513 | to the appropriate dummy value for its datatype. |
1514 | |
1515 | The dummy values are defined in the following package level constants: |
1516 | AR_TEXT_DUMMY |
1517 | AR_FLAG_DUMMY |
1518 | AR_NUMBER_DUMMY |
1519 | AR_DATE_DUMMY |
1520 | |
1521 | SCOPE - PUBLIC |
1522 | |
1523 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1524 | arp_util.debug |
1525 | |
1526 | ARGUMENTS : IN: |
1527 | None |
1528 | OUT: |
1529 | p_dist_rec - The record to initialize |
1530 | |
1531 | RETURNS : NONE |
1532 | |
1533 | NOTES |
1534 | |
1535 | MODIFICATION HISTORY |
1536 | 09-JUN-95 Charlie Tomberg Created |
1537 | |
1538 +===========================================================================*/
1539
1540 PROCEDURE set_to_dummy( p_dist_rec OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype) IS
1541
1542 BEGIN
1543
1544 arp_util.debug('arp_lgd_pkg.set_to_dummy()+');
1545
1546 p_dist_rec.cust_trx_line_gl_dist_id := AR_NUMBER_DUMMY;
1547 p_dist_rec.customer_trx_id := AR_NUMBER_DUMMY;
1548 p_dist_rec.customer_trx_line_id := AR_NUMBER_DUMMY;
1549 p_dist_rec.cust_trx_line_salesrep_id := AR_NUMBER_DUMMY;
1550 p_dist_rec.account_class := AR_TEXT_DUMMY;
1551 p_dist_rec.percent := AR_NUMBER_DUMMY;
1552 p_dist_rec.amount := AR_NUMBER_DUMMY;
1553 p_dist_rec.acctd_amount := AR_NUMBER_DUMMY;
1554 p_dist_rec.gl_date := AR_DATE_DUMMY;
1555 p_dist_rec.original_gl_date := AR_DATE_DUMMY;
1556 p_dist_rec.gl_posted_date := AR_DATE_DUMMY;
1557 p_dist_rec.code_combination_id := AR_NUMBER_DUMMY;
1558 p_dist_rec.concatenated_segments := AR_TEXT_DUMMY;
1559 p_dist_rec.collected_tax_ccid := AR_NUMBER_DUMMY;
1560 p_dist_rec.collected_tax_concat_seg := AR_TEXT_DUMMY;
1561 p_dist_rec.comments := AR_TEXT_DUMMY;
1562 p_dist_rec.account_set_flag := AR_FLAG_DUMMY;
1563 p_dist_rec.latest_rec_flag := AR_FLAG_DUMMY;
1564 p_dist_rec.rec_offset_flag := AR_FLAG_DUMMY; /* bug 3598021 - 3630436 */
1565 p_dist_rec.ussgl_transaction_code := AR_TEXT_DUMMY;
1566 p_dist_rec.ussgl_transaction_code_context := AR_TEXT_DUMMY;
1567 p_dist_rec.attribute_category := AR_TEXT_DUMMY;
1568 p_dist_rec.attribute1 := AR_TEXT_DUMMY;
1569 p_dist_rec.attribute2 := AR_TEXT_DUMMY;
1570 p_dist_rec.attribute3 := AR_TEXT_DUMMY;
1571 p_dist_rec.attribute4 := AR_TEXT_DUMMY;
1572 p_dist_rec.attribute5 := AR_TEXT_DUMMY;
1573 p_dist_rec.attribute6 := AR_TEXT_DUMMY;
1574 p_dist_rec.attribute7 := AR_TEXT_DUMMY;
1575 p_dist_rec.attribute8 := AR_TEXT_DUMMY;
1576 p_dist_rec.attribute9 := AR_TEXT_DUMMY;
1577 p_dist_rec.attribute10 := AR_TEXT_DUMMY;
1578 p_dist_rec.attribute11 := AR_TEXT_DUMMY;
1579 p_dist_rec.attribute12 := AR_TEXT_DUMMY;
1580 p_dist_rec.attribute13 := AR_TEXT_DUMMY;
1581 p_dist_rec.attribute14 := AR_TEXT_DUMMY;
1582 p_dist_rec.attribute15 := AR_TEXT_DUMMY;
1583 p_dist_rec.set_of_books_id := AR_NUMBER_DUMMY;
1584 p_dist_rec.posting_control_id := AR_NUMBER_DUMMY;
1585 p_dist_rec.last_update_date := AR_DATE_DUMMY;
1586 p_dist_rec.last_updated_by := AR_NUMBER_DUMMY;
1587 p_dist_rec.creation_date := AR_DATE_DUMMY;
1588 p_dist_rec.created_by := AR_NUMBER_DUMMY;
1589 p_dist_rec.last_update_login := AR_NUMBER_DUMMY;
1590 p_dist_rec.program_application_id := AR_NUMBER_DUMMY;
1591 p_dist_rec.program_id := AR_NUMBER_DUMMY;
1592 p_dist_rec.program_update_date := AR_DATE_DUMMY;
1593 p_dist_rec.rounding_correction_flag := AR_FLAG_DUMMY;
1594
1595 arp_util.debug('arp_lgd_pkg.set_to_dummy()-');
1596
1597 EXCEPTION
1598 WHEN OTHERS THEN
1599 arp_util.debug('EXCEPTION: arp_lgd_pkg.set_to_dummy()');
1600 RAISE;
1601
1602 END;
1603
1604
1605 /*===========================================================================+
1606 | PROCEDURE |
1607 | lock_p |
1608 | |
1609 | DESCRIPTION |
1610 | This procedure locks the ra_cust_trx_line_gl_dist row identified by |
1611 | p_cust_trx_line_gl_dist_id parameter. |
1612 | |
1613 | SCOPE - PUBLIC |
1614 | |
1615 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1616 | arp_util.debug |
1617 | |
1618 | ARGUMENTS : IN: |
1619 | p_cust_trx_line_gl_dist_id - identifies the row to lock |
1620 | OUT: |
1621 | None |
1622 | |
1623 | RETURNS : NONE |
1624 | |
1625 | NOTES |
1626 | |
1627 | MODIFICATION HISTORY |
1628 | 09-JUN-95 Charlie Tomberg Created |
1629 | |
1630 +===========================================================================*/
1631
1632 PROCEDURE lock_p( p_cust_trx_line_gl_dist_id
1633 IN ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type
1634 )
1635 IS
1636
1637 l_cust_trx_line_gl_dist_id
1638 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
1639
1640 BEGIN
1641 arp_util.debug('arp_ctlgd_pkg.lock_p()+');
1642
1643
1644 SELECT cust_trx_line_gl_dist_id
1645 INTO l_cust_trx_line_gl_dist_id
1646 FROM ra_cust_trx_line_gl_dist
1647 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
1648 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1649
1650 arp_util.debug('arp_ctlgd_pkg.lock_p()-');
1651
1652 EXCEPTION
1653 WHEN OTHERS THEN
1654 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_p' );
1655 RAISE;
1656 END;
1657
1658 /*===========================================================================+
1659 | PROCEDURE |
1660 | lock_f_ct_id |
1661 | |
1662 | DESCRIPTION |
1663 | This procedure locks the ra_cust_trx_line_gl_dist rows identified by |
1664 | p_customer_trx_id parameter. |
1665 | |
1666 | SCOPE - PUBLIC |
1667 | |
1668 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1669 | arp_util.debug |
1670 | |
1671 | ARGUMENTS : IN: |
1672 | p_customer_trx_id - identifies the rows to lock |
1673 | p_account_set_flag - value is used to restrict lock |
1674 | p_account_class - value is used to restrict lock |
1675 | OUT: |
1676 | None |
1677 | |
1678 | RETURNS : NONE |
1679 | |
1680 | NOTES |
1681 | |
1682 | MODIFICATION HISTORY |
1683 | 09-JUN-95 Charlie Tomberg Created |
1684 | |
1685 +===========================================================================*/
1686
1687 PROCEDURE lock_f_ct_id( p_customer_trx_id
1688 IN ra_customer_trx.customer_trx_id%type,
1689 p_account_set_flag
1690 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
1691 p_account_class
1692 IN ra_cust_trx_line_gl_dist.account_class%type)
1693 IS
1694
1695
1696 CURSOR lock_c IS
1697 SELECT cust_trx_line_gl_dist_id
1698 FROM ra_cust_trx_line_gl_dist
1699 WHERE customer_trx_id = p_customer_trx_id
1700 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
1701 AND account_class = nvl(p_account_class, account_class)
1702 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1703
1704
1705 BEGIN
1706 arp_util.debug('arp_ctlgd_pkg.lock_f_ct_id()+');
1707
1708 OPEN lock_c;
1709 CLOSE lock_c;
1710
1711 arp_util.debug('arp_ctlgd_pkg.lock_f_ct_id()-');
1712
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_f_ct_id' );
1716 RAISE;
1717 END;
1718
1719 /*===========================================================================+
1720 | PROCEDURE |
1721 | lock_f_ctl_id |
1722 | |
1723 | DESCRIPTION |
1724 | This procedure locks the ra_cust_trx_line_gl_dist rows identified by |
1725 | p_customer_trx_line_id parameter. |
1726 | |
1727 | SCOPE - PUBLIC |
1728 | |
1729 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1730 | arp_util.debug |
1731 | |
1732 | ARGUMENTS : IN: |
1733 | p_customer_trx_line_id - identifies the rows to lock |
1734 | p_account_set_flag - value is used to restrict lock |
1735 | p_account_class - value is used to restrict lock |
1736 | OUT: |
1737 | None |
1738 | |
1739 | RETURNS : NONE |
1740 | |
1741 | NOTES |
1742 | |
1743 | MODIFICATION HISTORY |
1744 | 09-JUN-95 Charlie Tomberg Created |
1745 | |
1746 +===========================================================================*/
1747
1748 PROCEDURE lock_f_ctl_id( p_customer_trx_line_id
1749 IN ra_customer_trx_lines.customer_trx_line_id%type,
1750 p_account_set_flag
1751 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
1752 p_account_class
1753 IN ra_cust_trx_line_gl_dist.account_class%type)
1754 IS
1755
1756 CURSOR lock_c IS
1757 SELECT cust_trx_line_gl_dist_id
1758 FROM ra_cust_trx_line_gl_dist
1759 WHERE customer_trx_line_id = p_customer_trx_line_id
1760 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
1761 AND account_class = nvl(p_account_class, account_class)
1762 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1763
1764 BEGIN
1765 arp_util.debug('arp_ctlgd_pkg.lock_f_ctl_id()+');
1766
1767 OPEN lock_c;
1768 CLOSE lock_c;
1769
1770 arp_util.debug('arp_ctlgd_pkg.lock_f_ctl_id()-');
1771
1772 EXCEPTION
1773 WHEN OTHERS THEN
1774 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_f_ctl_id' );
1775 RAISE;
1776 END;
1777
1778
1779 /*===========================================================================+
1780 | PROCEDURE |
1781 | lock_f_ctls_id |
1782 | |
1783 | DESCRIPTION |
1784 | This procedure locks the ra_cust_trx_line_gl_dist rows identified by |
1785 | p_cust_trx_line_salesrep_id parameter. |
1786 | |
1787 | SCOPE - PUBLIC |
1788 | |
1789 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1790 | arp_util.debug |
1791 | |
1792 | ARGUMENTS : IN: |
1793 | p_cust_trx_line_salesrep_id - identifies the rows to lock |
1794 | p_account_set_flag - value is used to restrict lock |
1795 | p_account_class - value is used to restrict lock |
1796 | OUT: |
1797 | None |
1798 | |
1799 | RETURNS : NONE |
1800 | |
1801 | NOTES |
1802 | |
1803 | MODIFICATION HISTORY |
1804 | 09-JUN-95 Charlie Tomberg Created |
1805 | |
1806 +===========================================================================*/
1807
1808 PROCEDURE lock_f_ctls_id( p_cust_trx_line_salesrep_id
1809 IN ra_cust_trx_line_gl_dist.cust_trx_line_salesrep_id%type,
1810 p_account_set_flag
1811 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
1812 p_account_class
1813 IN ra_cust_trx_line_gl_dist.account_class%type)
1814 IS
1815
1816 CURSOR lock_c IS
1817 SELECT cust_trx_line_gl_dist_id
1818 FROM ra_cust_trx_line_gl_dist
1819 WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
1820 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
1821 AND account_class = nvl(p_account_class, account_class)
1822 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1823
1824 BEGIN
1825 arp_util.debug('arp_ctlgd_pkg.lock_f_ctls_id()+');
1826
1827 OPEN lock_c;
1828 CLOSE lock_c;
1829
1830 arp_util.debug('arp_ctlgd_pkg.lock_f_ctls_id()-');
1831
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_f_ctls_id' );
1835 RAISE;
1836 END;
1837
1838 /*===========================================================================+
1839 | PROCEDURE |
1840 | lock_fetch_p |
1841 | |
1842 | DESCRIPTION |
1843 | This procedure locks the ra_cust_trx_line_gl_dist row identified |
1844 | by the p_cust_trx_line_gl_dist_id parameter and populates the |
1845 | p_dist_rec parameter with the row that was locked. |
1846 | |
1847 | SCOPE - PUBLIC |
1848 | |
1849 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1850 | arp_util.debug |
1851 | |
1852 | ARGUMENTS : IN: |
1853 | p_cust_trx_line_gl_dist_id - identifies the row to lock |
1854 | OUT: |
1855 | p_dist_rec - contains the locked row |
1856 | |
1857 | RETURNS : NONE |
1858 | |
1859 | NOTES |
1860 | |
1861 | MODIFICATION HISTORY |
1862 | 09-JUN-95 Charlie Tomberg Created |
1863 | |
1864 +===========================================================================*/
1865
1866 PROCEDURE lock_fetch_p( p_dist_rec IN OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype,
1867 p_cust_trx_line_gl_dist_id IN
1868 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type
1869 ) IS
1870
1871 BEGIN
1872 arp_util.debug('arp_ctlgd_pkg.lock_fetch_p()+');
1873
1874 SELECT *
1875 INTO p_dist_rec
1876 FROM ra_cust_trx_line_gl_dist
1877 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
1878 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
1879
1880 arp_util.debug('arp_ctlgd_pkg.lock_fetch_p()-');
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_fetch_p' );
1885 RAISE;
1886 END;
1887
1888 /*===========================================================================+
1889 | PROCEDURE |
1890 | lock_compare_p |
1891 | |
1892 | DESCRIPTION |
1893 | This procedure locks the ra_cust_trx_line_gl_dist row identified |
1894 | by the p_cust_trx_line_gl_dist_id parameter only if no columns in |
1895 | that row have changed from when they were first selected in the form. |
1896 | |
1897 | SCOPE - PUBLIC |
1898 | |
1899 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1900 | arp_util.debug |
1901 | |
1902 | ARGUMENTS : IN: |
1903 | p_cust_trx_line_gl_dist_id - identifies the row to lock |
1904 | p_dist_rec - dist record for comparison |
1905 | p_ignore_who_flag - directs system to ignore who cols |
1906 | OUT: |
1907 | None |
1908 | |
1909 | RETURNS : NONE |
1910 | |
1911 | NOTES |
1912 | |
1913 | MODIFICATION HISTORY |
1914 | 09-JUN-95 Charlie Tomberg Created |
1915 | 29-JUN-95 Charlie Tomberg Modified to use select for update |
1916 | 13-OCT-95 Martin Johnson Handle NO_DATA_FOUND exception |
1917 | |
1918 +===========================================================================*/
1919
1920 PROCEDURE lock_compare_p( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
1921 p_cust_trx_line_gl_dist_id IN
1922 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
1923 p_ignore_who_flag BOOLEAN DEFAULT FALSE) IS
1924
1925 l_new_dist_rec ra_cust_trx_line_gl_dist%rowtype;
1926 l_temp_dist_rec ra_cust_trx_line_gl_dist%rowtype;
1927 l_ignore_who_flag varchar2(2);
1928
1929 BEGIN
1930 arp_util.debug('arp_ctlgd_pkg.lock_compare_p()+');
1931
1932 IF (p_ignore_who_flag = TRUE)
1933 THEN l_ignore_who_flag := 'Y';
1934 ELSE l_ignore_who_flag := 'N';
1935 END IF;
1936
1937 SELECT *
1938 INTO l_new_dist_rec
1939 FROM ra_cust_trx_line_gl_dist ctlgd
1940 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
1941 AND (
1942 NVL(ctlgd.cust_trx_line_gl_dist_id, AR_NUMBER_DUMMY) =
1943 NVL(
1944 DECODE(p_dist_rec.cust_trx_line_gl_dist_id,
1945 AR_NUMBER_DUMMY, ctlgd.cust_trx_line_gl_dist_id,
1946 p_dist_rec.cust_trx_line_gl_dist_id),
1947 AR_NUMBER_DUMMY
1948 )
1949 AND
1950 NVL(ctlgd.customer_trx_id, AR_NUMBER_DUMMY) =
1951 NVL(
1952 DECODE(p_dist_rec.customer_trx_id,
1953 AR_NUMBER_DUMMY, ctlgd.customer_trx_id,
1954 p_dist_rec.customer_trx_id),
1955 AR_NUMBER_DUMMY
1956 )
1957 AND
1958 NVL(ctlgd.customer_trx_line_id, AR_NUMBER_DUMMY) =
1959 NVL(
1960 DECODE(p_dist_rec.customer_trx_line_id,
1961 AR_NUMBER_DUMMY, ctlgd.customer_trx_line_id,
1962 p_dist_rec.customer_trx_line_id),
1963 AR_NUMBER_DUMMY
1964 )
1965 AND
1966 NVL(ctlgd.cust_trx_line_salesrep_id, AR_NUMBER_DUMMY) =
1967 NVL(
1968 DECODE(p_dist_rec.cust_trx_line_salesrep_id,
1969 AR_NUMBER_DUMMY, ctlgd.cust_trx_line_salesrep_id,
1970 p_dist_rec.cust_trx_line_salesrep_id),
1971 AR_NUMBER_DUMMY
1972 )
1973 AND
1974 NVL(ctlgd.account_class, AR_TEXT_DUMMY) =
1975 NVL(
1976 DECODE(p_dist_rec.account_class,
1977 AR_TEXT_DUMMY, ctlgd.account_class,
1978 p_dist_rec.account_class),
1979 AR_TEXT_DUMMY
1980 )
1981 AND
1982 NVL(ctlgd.percent, AR_NUMBER_DUMMY) =
1983 NVL(
1984 DECODE(p_dist_rec.percent,
1985 AR_NUMBER_DUMMY, ctlgd.percent,
1986 p_dist_rec.percent),
1987 AR_NUMBER_DUMMY
1988 )
1989 AND
1990 NVL(ctlgd.amount, AR_NUMBER_DUMMY) =
1991 NVL(
1992 DECODE(p_dist_rec.amount,
1993 AR_NUMBER_DUMMY, ctlgd.amount,
1994 p_dist_rec.amount),
1995 AR_NUMBER_DUMMY
1996 )
1997 AND
1998 NVL(ctlgd.acctd_amount, AR_NUMBER_DUMMY) =
1999 NVL(
2000 DECODE(p_dist_rec.acctd_amount,
2001 AR_NUMBER_DUMMY, ctlgd.acctd_amount,
2002 p_dist_rec.acctd_amount),
2003 AR_NUMBER_DUMMY
2004 )
2005 AND
2006 NVL(ctlgd.gl_date, AR_DATE_DUMMY) =
2007 NVL(
2008 DECODE(p_dist_rec.gl_date,
2009 AR_DATE_DUMMY, ctlgd.gl_date,
2010 p_dist_rec.gl_date),
2011 AR_DATE_DUMMY
2012 )
2013 AND
2014 NVL(ctlgd.original_gl_date, AR_DATE_DUMMY) =
2015 NVL(
2016 DECODE(p_dist_rec.original_gl_date,
2017 AR_DATE_DUMMY, ctlgd.original_gl_date,
2018 p_dist_rec.original_gl_date),
2019 AR_DATE_DUMMY
2020 )
2021 AND
2022 NVL(ctlgd.gl_posted_date, AR_DATE_DUMMY) =
2023 NVL(
2024 DECODE(p_dist_rec.gl_posted_date,
2025 AR_DATE_DUMMY, ctlgd.gl_posted_date,
2026 p_dist_rec.gl_posted_date),
2027 AR_DATE_DUMMY
2028 )
2029 AND
2030 NVL(ctlgd.code_combination_id, AR_NUMBER_DUMMY) =
2031 NVL(
2032 DECODE(p_dist_rec.code_combination_id,
2033 AR_NUMBER_DUMMY, ctlgd.code_combination_id,
2034 p_dist_rec.code_combination_id),
2035 AR_NUMBER_DUMMY
2036 )
2037 AND
2038 NVL(ctlgd.concatenated_segments, AR_TEXT_DUMMY) =
2039 NVL(
2040 DECODE(p_dist_rec.concatenated_segments,
2041 AR_TEXT_DUMMY, ctlgd.concatenated_segments,
2042 p_dist_rec.concatenated_segments),
2043 AR_TEXT_DUMMY
2044 )
2045 AND
2046 NVL(ctlgd.collected_tax_ccid, AR_NUMBER_DUMMY) =
2047 NVL(
2048 DECODE(p_dist_rec.collected_tax_ccid,
2049 AR_NUMBER_DUMMY, ctlgd.collected_tax_ccid,
2050 p_dist_rec.collected_tax_ccid),
2051 AR_NUMBER_DUMMY
2052 )
2053 AND
2054 NVL(ctlgd.collected_tax_concat_seg, AR_TEXT_DUMMY) =
2055 NVL(
2056 DECODE(p_dist_rec.collected_tax_concat_seg,
2057 AR_TEXT_DUMMY, ctlgd.collected_tax_concat_seg,
2058 p_dist_rec.collected_tax_concat_seg),
2059 AR_TEXT_DUMMY
2060 )
2061 AND
2062 NVL(ctlgd.comments, AR_TEXT_DUMMY) =
2063 NVL(
2064 DECODE(p_dist_rec.comments,
2065 AR_TEXT_DUMMY, ctlgd.comments,
2066 p_dist_rec.comments),
2067 AR_TEXT_DUMMY
2068 )
2069 AND
2070 NVL(ctlgd.account_set_flag, AR_FLAG_DUMMY) =
2071 NVL(
2072 DECODE(p_dist_rec.account_set_flag,
2073 AR_FLAG_DUMMY, ctlgd.account_set_flag,
2074 p_dist_rec.account_set_flag),
2075 AR_FLAG_DUMMY
2076 )
2077 AND
2078 NVL(ctlgd.latest_rec_flag, AR_FLAG_DUMMY) =
2079 NVL(
2080 DECODE(p_dist_rec.latest_rec_flag,
2081 AR_FLAG_DUMMY, ctlgd.latest_rec_flag,
2082 p_dist_rec.latest_rec_flag),
2083 AR_FLAG_DUMMY
2084 )
2085 AND
2086 NVL(ctlgd.ussgl_transaction_code, AR_TEXT_DUMMY) =
2087 NVL(
2088 DECODE(p_dist_rec.ussgl_transaction_code,
2089 AR_TEXT_DUMMY, ctlgd.ussgl_transaction_code,
2090 p_dist_rec.ussgl_transaction_code),
2091 AR_TEXT_DUMMY
2092 )
2093 AND
2094 NVL(ctlgd.ussgl_transaction_code_context, AR_TEXT_DUMMY) =
2095 NVL(
2096 DECODE(p_dist_rec.ussgl_transaction_code_context,
2097 AR_TEXT_DUMMY, ctlgd.ussgl_transaction_code_context,
2098 p_dist_rec.ussgl_transaction_code_context),
2099 AR_TEXT_DUMMY
2100 )
2101 AND
2102 NVL(ctlgd.attribute_category, AR_TEXT_DUMMY) =
2103 NVL(
2104 DECODE(p_dist_rec.attribute_category,
2105 AR_TEXT_DUMMY, ctlgd.attribute_category,
2106 p_dist_rec.attribute_category),
2107 AR_TEXT_DUMMY
2108 )
2109 AND
2110 NVL(ctlgd.attribute1, AR_TEXT_DUMMY) =
2111 NVL(
2112 DECODE(p_dist_rec.attribute1,
2113 AR_TEXT_DUMMY, ctlgd.attribute1,
2114 p_dist_rec.attribute1),
2115 AR_TEXT_DUMMY
2116 )
2117 AND
2118 NVL(ctlgd.attribute2, AR_TEXT_DUMMY) =
2119 NVL(
2120 DECODE(p_dist_rec.attribute2,
2121 AR_TEXT_DUMMY, ctlgd.attribute2,
2122 p_dist_rec.attribute2),
2123 AR_TEXT_DUMMY
2124 )
2125 AND
2126 NVL(ctlgd.attribute3, AR_TEXT_DUMMY) =
2127 NVL(
2128 DECODE(p_dist_rec.attribute3,
2129 AR_TEXT_DUMMY, ctlgd.attribute3,
2130 p_dist_rec.attribute3),
2131 AR_TEXT_DUMMY
2132 )
2133 AND
2134 NVL(ctlgd.attribute4, AR_TEXT_DUMMY) =
2135 NVL(
2136 DECODE(p_dist_rec.attribute4,
2137 AR_TEXT_DUMMY, ctlgd.attribute4,
2138 p_dist_rec.attribute4),
2139 AR_TEXT_DUMMY
2140 )
2141 AND
2142 NVL(ctlgd.attribute5, AR_TEXT_DUMMY) =
2143 NVL(
2144 DECODE(p_dist_rec.attribute5,
2145 AR_TEXT_DUMMY, ctlgd.attribute5,
2146 p_dist_rec.attribute5),
2147 AR_TEXT_DUMMY
2148 )
2149 AND
2150 NVL(ctlgd.attribute6, AR_TEXT_DUMMY) =
2151 NVL(
2152 DECODE(p_dist_rec.attribute6,
2153 AR_TEXT_DUMMY, ctlgd.attribute6,
2154 p_dist_rec.attribute6),
2155 AR_TEXT_DUMMY
2156 )
2157 AND
2158 NVL(ctlgd.attribute7, AR_TEXT_DUMMY) =
2159 NVL(
2160 DECODE(p_dist_rec.attribute7,
2161 AR_TEXT_DUMMY, ctlgd.attribute7,
2162 p_dist_rec.attribute7),
2163 AR_TEXT_DUMMY
2164 )
2165 AND
2166 NVL(ctlgd.attribute8, AR_TEXT_DUMMY) =
2167 NVL(
2168 DECODE(p_dist_rec.attribute8,
2169 AR_TEXT_DUMMY, ctlgd.attribute8,
2170 p_dist_rec.attribute8),
2171 AR_TEXT_DUMMY
2172 )
2173 AND
2174 NVL(ctlgd.attribute9, AR_TEXT_DUMMY) =
2175 NVL(
2176 DECODE(p_dist_rec.attribute9,
2177 AR_TEXT_DUMMY, ctlgd.attribute9,
2178 p_dist_rec.attribute9),
2179 AR_TEXT_DUMMY
2180 )
2181 AND
2182 NVL(ctlgd.attribute10, AR_TEXT_DUMMY) =
2183 NVL(
2184 DECODE(p_dist_rec.attribute10,
2185 AR_TEXT_DUMMY, ctlgd.attribute10,
2186 p_dist_rec.attribute10),
2187 AR_TEXT_DUMMY
2188 )
2189 AND
2190 NVL(ctlgd.attribute11, AR_TEXT_DUMMY) =
2191 NVL(
2192 DECODE(p_dist_rec.attribute11,
2193 AR_TEXT_DUMMY, ctlgd.attribute11,
2194 p_dist_rec.attribute11),
2195 AR_TEXT_DUMMY
2196 )
2197 AND
2198 NVL(ctlgd.attribute12, AR_TEXT_DUMMY) =
2199 NVL(
2200 DECODE(p_dist_rec.attribute12,
2201 AR_TEXT_DUMMY, ctlgd.attribute12,
2202 p_dist_rec.attribute12),
2203 AR_TEXT_DUMMY
2204 )
2205 AND
2206 NVL(ctlgd.attribute13, AR_TEXT_DUMMY) =
2207 NVL(
2208 DECODE(p_dist_rec.attribute13,
2209 AR_TEXT_DUMMY, ctlgd.attribute13,
2210 p_dist_rec.attribute13),
2211 AR_TEXT_DUMMY
2212 )
2213 AND
2214 NVL(ctlgd.attribute14, AR_TEXT_DUMMY) =
2215 NVL(
2216 DECODE(p_dist_rec.attribute14,
2217 AR_TEXT_DUMMY, ctlgd.attribute14,
2218 p_dist_rec.attribute14),
2219 AR_TEXT_DUMMY
2220 )
2221 AND
2222 NVL(ctlgd.attribute15, AR_TEXT_DUMMY) =
2223 NVL(
2224 DECODE(p_dist_rec.attribute15,
2225 AR_TEXT_DUMMY, ctlgd.attribute15,
2226 p_dist_rec.attribute15),
2227 AR_TEXT_DUMMY
2228 )
2229 AND
2230 NVL(ctlgd.set_of_books_id, AR_NUMBER_DUMMY) =
2231 NVL(
2232 DECODE(p_dist_rec.set_of_books_id,
2233 AR_NUMBER_DUMMY, ctlgd.set_of_books_id,
2234 p_dist_rec.set_of_books_id),
2235 AR_NUMBER_DUMMY
2236 )
2237 AND
2238 NVL(ctlgd.posting_control_id, AR_NUMBER_DUMMY) =
2239 NVL(
2240 DECODE(p_dist_rec.posting_control_id,
2241 AR_NUMBER_DUMMY, ctlgd.posting_control_id,
2242 p_dist_rec.posting_control_id),
2243 AR_NUMBER_DUMMY
2244 )
2245 AND
2246 NVL(ctlgd.last_update_date, AR_DATE_DUMMY) =
2247 NVL(
2248 DECODE(l_ignore_who_flag,
2249 'Y', NVL(ctlgd.last_update_date, AR_DATE_DUMMY),
2250 DECODE(
2251 p_dist_rec.last_update_date,
2252 AR_DATE_DUMMY, ctlgd.last_update_date,
2253 p_dist_rec.last_update_date
2254 )
2255 ),
2256 AR_DATE_DUMMY
2257 )
2258 AND
2259 NVL(ctlgd.last_updated_by, AR_NUMBER_DUMMY) =
2260 NVL(
2261 DECODE(l_ignore_who_flag,
2262 'Y', NVL(ctlgd.last_updated_by, AR_NUMBER_DUMMY),
2263 DECODE(
2264 p_dist_rec.last_updated_by,
2265 AR_NUMBER_DUMMY, ctlgd.last_updated_by,
2266 p_dist_rec.last_updated_by
2267 )
2268 ),
2269 AR_NUMBER_DUMMY
2270 )
2271 AND
2272 NVL(ctlgd.creation_date, AR_DATE_DUMMY) =
2273 NVL(
2274 DECODE(l_ignore_who_flag,
2275 'Y', NVL(ctlgd.creation_date, AR_DATE_DUMMY),
2276 DECODE(
2277 p_dist_rec.creation_date,
2278 AR_DATE_DUMMY, ctlgd.creation_date,
2279 p_dist_rec.creation_date
2280 )
2281 ),
2282 AR_DATE_DUMMY
2283 )
2284 AND
2285 NVL(ctlgd.created_by, AR_NUMBER_DUMMY) =
2286 NVL(
2287 DECODE(l_ignore_who_flag,
2288 'Y', NVL(ctlgd.created_by, AR_NUMBER_DUMMY),
2289 DECODE(
2290 p_dist_rec.created_by,
2291 AR_NUMBER_DUMMY, ctlgd.created_by,
2292 p_dist_rec.created_by
2293 )
2294 ),
2295 AR_NUMBER_DUMMY
2296 )
2297 AND
2298 NVL(ctlgd.last_update_login, AR_NUMBER_DUMMY) =
2299 NVL(
2300 DECODE(l_ignore_who_flag,
2301 'Y', NVL(ctlgd.last_update_login, AR_NUMBER_DUMMY),
2302 DECODE(
2303 p_dist_rec.last_update_login,
2304 AR_NUMBER_DUMMY, ctlgd.last_update_login,
2305 p_dist_rec.last_update_login
2306 )
2307 ),
2308 AR_NUMBER_DUMMY
2309 )
2310 AND
2311 NVL(ctlgd.program_application_id, AR_NUMBER_DUMMY) =
2312 NVL(
2313 DECODE(l_ignore_who_flag,
2314 'Y', NVL(ctlgd.program_application_id, AR_NUMBER_DUMMY),
2315 DECODE(
2316 p_dist_rec.program_application_id,
2317 AR_NUMBER_DUMMY, ctlgd.program_application_id,
2318 p_dist_rec.program_application_id
2319 )
2320 ),
2321 AR_NUMBER_DUMMY
2322 )
2323 AND
2324 NVL(ctlgd.program_id, AR_NUMBER_DUMMY) =
2325 NVL(
2326 DECODE(l_ignore_who_flag,
2327 'Y', NVL(ctlgd.program_id, AR_NUMBER_DUMMY),
2328 DECODE(
2329 p_dist_rec.program_id,
2330 AR_NUMBER_DUMMY, ctlgd.program_id,
2331 p_dist_rec.program_id
2332 )
2333 ),
2334 AR_NUMBER_DUMMY
2335 )
2336 AND
2337 NVL(ctlgd.program_update_date, AR_DATE_DUMMY) =
2338 NVL(
2339 DECODE(l_ignore_who_flag,
2340 'Y', NVL(ctlgd.program_update_date, AR_DATE_DUMMY),
2341 DECODE(
2342 p_dist_rec.program_update_date,
2343 AR_DATE_DUMMY, ctlgd.program_update_date,
2344 p_dist_rec.program_update_date
2345 )
2346 ),
2347 AR_DATE_DUMMY
2348 )
2349 )
2350 FOR UPDATE OF cust_trx_line_gl_dist_id NOWAIT;
2351
2352 arp_util.debug('arp_ctlgd_pkg.lock_compare_p()-');
2353
2354 EXCEPTION
2355 WHEN NO_DATA_FOUND THEN
2356
2357 arp_util.debug('');
2358 arp_util.debug('p_cust_trx_line_gl_dist_id = ' ||
2359 p_cust_trx_line_gl_dist_id );
2360 arp_util.debug('-------- new dist record --------');
2361 display_dist_rec( p_dist_rec );
2362
2363 arp_util.debug('');
2364
2365 arp_util.debug('-------- old dist record --------');
2366
2367 fetch_p( l_temp_dist_rec,
2368 p_cust_trx_line_gl_dist_id );
2369
2370 display_dist_rec( l_temp_dist_rec );
2371
2372 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2373 APP_EXCEPTION.Raise_Exception;
2374
2375
2376 WHEN OTHERS THEN
2377 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.lock_compare_p' );
2378
2379 arp_util.debug( SQLERRM );
2380
2381 arp_util.debug('----- parameters for lock_compare_p -----');
2382
2383 arp_util.debug('p_cust_trx_line_gl_dist_id = ' ||
2384 p_cust_trx_line_gl_dist_id );
2385 arp_util.debug('p_ignore_who_flag =' ||
2386 arp_trx_util.boolean_to_varchar2(p_ignore_who_flag));
2387
2388 arp_util.debug('');
2389 arp_util.debug('-------- new dist record --------');
2390 display_dist_rec( p_dist_rec );
2391
2392 arp_util.debug('');
2393
2394 arp_util.debug('-------- old dist record --------');
2395
2396 fetch_p( l_temp_dist_rec,
2397 p_cust_trx_line_gl_dist_id );
2398
2399 display_dist_rec( l_temp_dist_rec );
2400
2401 RAISE;
2402 END;
2403
2404 /*===========================================================================+
2405 | PROCEDURE |
2406 | lock_compare_cover |
2407 | |
2408 | DESCRIPTION |
2409 | Converts column parameters to a dist record and |
2410 | lockss a dist line. |
2411 | |
2412 | SCOPE - PUBLIC |
2413 | |
2414 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2415 | arp_util.debug |
2416 | |
2417 | ARGUMENTS : IN: |
2418 | p_cust_trx_line_gl_dist_id |
2419 | p_customer_trx_id |
2420 | p_customer_trx_line_id |
2421 | p_cust_trx_line_salesrep_id |
2422 | p_account_class |
2423 | p_percent |
2424 | p_amount |
2425 | p_acctd_amount |
2426 | p_gl_date |
2427 | p_original_gl_date |
2428 | p_gl_posted_date |
2429 | p_code_combination_id |
2430 | p_concatenated_segments |
2431 | p_collected_tax_ccid |
2432 | p_collected_tax_concat_seg |
2433 | p_comments |
2434 | p_account_set_flag |
2435 | p_latest_rec_flag |
2436 | p_ussgl_transaction_code |
2437 | p_ussgl_trx_code_context |
2438 | p_attribute_category |
2439 | p_attribute1 |
2440 | p_attribute2 |
2441 | p_attribute3 |
2442 | p_attribute4 |
2443 | p_attribute5 |
2444 | p_attribute6 |
2445 | p_attribute7 |
2446 | p_attribute8 |
2447 | p_attribute9 |
2448 | p_attribute10 |
2449 | p_attribute11 |
2450 | p_attribute12 |
2451 | p_attribute13 |
2452 | p_attribute14 |
2453 | p_attribute15 |
2454 | p_posting_control_id |
2455 | OUT: |
2456 | None |
2457 | IN/ OUT: |
2458 | None |
2459 | |
2460 | RETURNS : NONE |
2461 | |
2462 | NOTES |
2463 | |
2464 | MODIFICATION HISTORY |
2465 | 13-OCT-95 Martin Johnson Created |
2466 | |
2467 +===========================================================================*/
2468
2469
2470 PROCEDURE lock_compare_cover(
2471 p_cust_trx_line_gl_dist_id IN
2472 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
2473 p_customer_trx_id IN
2474 ra_cust_trx_line_gl_dist.customer_trx_id%type,
2475 p_customer_trx_line_id IN
2476 ra_cust_trx_line_gl_dist.customer_trx_line_id %type,
2477 p_cust_trx_line_salesrep_id IN
2478 ra_cust_trx_line_gl_dist.cust_trx_line_salesrep_id%type,
2479 p_account_class IN
2480 ra_cust_trx_line_gl_dist.account_class%type,
2481 p_percent IN
2482 ra_cust_trx_line_gl_dist.percent%type,
2483 p_amount IN
2484 ra_cust_trx_line_gl_dist.amount%type,
2485 p_gl_date IN
2486 ra_cust_trx_line_gl_dist.gl_date%type,
2487 p_original_gl_date IN
2488 ra_cust_trx_line_gl_dist.original_gl_date%type,
2489 p_gl_posted_date IN
2490 ra_cust_trx_line_gl_dist.gl_posted_date%type,
2491 p_code_combination_id IN
2492 ra_cust_trx_line_gl_dist.code_combination_id%type,
2493 p_concatenated_segments IN
2494 ra_cust_trx_line_gl_dist.concatenated_segments%type,
2495 p_collected_tax_ccid IN
2496 ra_cust_trx_line_gl_dist.collected_tax_ccid%type,
2497 p_collected_tax_concat_seg IN
2498 ra_cust_trx_line_gl_dist.collected_tax_concat_seg%type,
2499 p_comments IN
2500 ra_cust_trx_line_gl_dist.comments%type,
2501 p_account_set_flag IN
2502 ra_cust_trx_line_gl_dist.account_set_flag%type,
2503 p_latest_rec_flag IN
2504 ra_cust_trx_line_gl_dist.latest_rec_flag%type,
2505 p_ussgl_transaction_code IN
2506 ra_cust_trx_line_gl_dist.ussgl_transaction_code%type,
2507 p_ussgl_trx_code_context IN
2508 ra_cust_trx_line_gl_dist.ussgl_transaction_code_context%type,
2509 p_attribute_category IN
2510 ra_cust_trx_line_gl_dist.attribute_category%type,
2511 p_attribute1 IN
2512 ra_cust_trx_line_gl_dist.attribute1%type,
2513 p_attribute2 IN
2514 ra_cust_trx_line_gl_dist.attribute2%type,
2515 p_attribute3 IN
2516 ra_cust_trx_line_gl_dist.attribute3%type,
2517 p_attribute4 IN
2518 ra_cust_trx_line_gl_dist.attribute4%type,
2519 p_attribute5 IN
2520 ra_cust_trx_line_gl_dist.attribute5%type,
2521 p_attribute6 IN
2522 ra_cust_trx_line_gl_dist.attribute6%type,
2523 p_attribute7 IN
2524 ra_cust_trx_line_gl_dist.attribute7%type,
2525 p_attribute8 IN
2526 ra_cust_trx_line_gl_dist.attribute8%type,
2527 p_attribute9 IN
2528 ra_cust_trx_line_gl_dist.attribute9%type,
2529 p_attribute10 IN
2530 ra_cust_trx_line_gl_dist.attribute10%type,
2531 p_attribute11 IN
2532 ra_cust_trx_line_gl_dist.attribute11%type,
2533 p_attribute12 IN
2534 ra_cust_trx_line_gl_dist.attribute12%type,
2535 p_attribute13 IN
2536 ra_cust_trx_line_gl_dist.attribute13%type,
2537 p_attribute14 IN
2538 ra_cust_trx_line_gl_dist.attribute14%type,
2539 p_attribute15 IN
2540 ra_cust_trx_line_gl_dist.attribute15%type,
2541 p_posting_control_id IN
2542 ra_cust_trx_line_gl_dist.posting_control_id%type )
2543 IS
2544
2545 l_dist_rec ra_cust_trx_line_gl_dist%rowtype;
2546
2547 BEGIN
2548
2549 IF PG_DEBUG in ('Y', 'C') THEN
2550 arp_util.debug('arp_ctlgd_pkg.lock_compare_cover()+',
2551 pg_msg_level_debug);
2552 END IF;
2553
2554 /*------------------------------------------------+
2555 | Populate the dist record group with |
2556 | the values passed in as parameters. |
2557 +------------------------------------------------*/
2558
2559 set_to_dummy(l_dist_rec);
2560
2561 l_dist_rec.cust_trx_line_gl_dist_id := p_cust_trx_line_gl_dist_id;
2562 l_dist_rec.customer_trx_id := p_customer_trx_id;
2563 l_dist_rec.customer_trx_line_id := p_customer_trx_line_id;
2564 l_dist_rec.cust_trx_line_salesrep_id := p_cust_trx_line_salesrep_id;
2565 l_dist_rec.account_class := p_account_class;
2566 l_dist_rec.percent := p_percent;
2567 l_dist_rec.amount := p_amount;
2568 l_dist_rec.gl_date := p_gl_date;
2569 l_dist_rec.original_gl_date := p_original_gl_date;
2570 l_dist_rec.gl_posted_date := p_gl_posted_date;
2571 l_dist_rec.code_combination_id := p_code_combination_id;
2572 l_dist_rec.concatenated_segments := p_concatenated_segments;
2573 l_dist_rec.collected_tax_ccid := p_collected_tax_ccid;
2574 l_dist_rec.collected_tax_concat_seg := p_collected_tax_concat_seg;
2575 l_dist_rec.comments := p_comments;
2576 l_dist_rec.account_set_flag := p_account_set_flag;
2577 l_dist_rec.latest_rec_flag := p_latest_rec_flag;
2578 l_dist_rec.ussgl_transaction_code := p_ussgl_transaction_code;
2579 l_dist_rec.ussgl_transaction_code_context := p_ussgl_trx_code_context;
2580 l_dist_rec.attribute_category := p_attribute_category;
2581 l_dist_rec.attribute1 := p_attribute1;
2582 l_dist_rec.attribute2 := p_attribute2;
2583 l_dist_rec.attribute3 := p_attribute3;
2584 l_dist_rec.attribute4 := p_attribute4;
2585 l_dist_rec.attribute5 := p_attribute5;
2586 l_dist_rec.attribute6 := p_attribute6;
2587 l_dist_rec.attribute7 := p_attribute7;
2588 l_dist_rec.attribute8 := p_attribute8;
2589 l_dist_rec.attribute9 := p_attribute9;
2590 l_dist_rec.attribute10 := p_attribute10;
2591 l_dist_rec.attribute11 := p_attribute11;
2592 l_dist_rec.attribute12 := p_attribute12;
2593 l_dist_rec.attribute13 := p_attribute13;
2594 l_dist_rec.attribute14 := p_attribute14;
2595 l_dist_rec.attribute15 := p_attribute15;
2596 l_dist_rec.posting_control_id := p_posting_control_id;
2597
2598 /*----------------------------------------------+
2599 | Call the standard dist table handler |
2600 +----------------------------------------------*/
2601
2602 lock_compare_p(
2603 l_dist_rec,
2604 p_cust_trx_line_gl_dist_id,
2605 TRUE -- ignore who columns
2606 );
2607
2608 IF PG_DEBUG in ('Y', 'C') THEN
2609 arp_util.debug('arp_ctlgd_pkg.lock_compare_cover()-',
2610 pg_msg_level_debug);
2611 END IF;
2612
2613 EXCEPTION
2614 WHEN OTHERS THEN
2615
2616 IF PG_DEBUG in ('Y', 'C') THEN
2617 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.lock_compare_cover()',
2618 pg_msg_level_debug);
2619 arp_util.debug('lock_compare_cover: ' || '------- parameters for lock_compare_cove() ' ||
2620 '---------',
2621 pg_msg_level_debug);
2622 arp_util.debug('lock_compare_cover: ' || 'p_cust_trx_line_gl_dist_id = ' ||
2623 p_cust_trx_line_gl_dist_id,
2624 pg_msg_level_debug);
2625 arp_util.debug('lock_compare_cover: ' || 'p_customer_trx_id = ' || p_customer_trx_id,
2626 pg_msg_level_debug);
2627 arp_util.debug('lock_compare_cover: ' || 'p_customer_trx_line_id = ' || p_customer_trx_line_id,
2628 pg_msg_level_debug);
2629 arp_util.debug('lock_compare_cover: ' || 'p_cust_trx_line_salesrep_id = ' ||
2630 p_cust_trx_line_salesrep_id,
2631 pg_msg_level_debug);
2632 arp_util.debug('lock_compare_cover: ' || 'p_account_class = ' || p_account_class,
2633 pg_msg_level_debug);
2634 arp_util.debug('lock_compare_cover: ' || 'p_percent = ' || p_percent,
2635 pg_msg_level_debug);
2636 arp_util.debug('lock_compare_cover: ' || 'p_amount = ' || p_amount,
2637 pg_msg_level_debug);
2638 arp_util.debug('lock_compare_cover: ' || 'p_gl_date = ' || p_gl_date,
2639 pg_msg_level_debug);
2640 arp_util.debug('lock_compare_cover: ' || 'p_gl_posted_date = ' || p_gl_posted_date,
2641 pg_msg_level_debug);
2642 arp_util.debug('lock_compare_cover: ' || 'p_original_gl_date = ' || p_original_gl_date,
2643 pg_msg_level_debug);
2644 arp_util.debug('lock_compare_cover: ' || 'p_code_combination_id = ' || p_code_combination_id,
2645 pg_msg_level_debug);
2646 arp_util.debug('lock_compare_cover: ' || 'p_concatenated_segments = ' || p_concatenated_segments,
2647 pg_msg_level_debug);
2648 arp_util.debug('lock_compare_cover: ' || 'p_collected_tax_ccid = ' || p_collected_tax_ccid,
2649 pg_msg_level_debug);
2650 arp_util.debug('lock_compare_cover: ' || 'p_collected_tax_concat_seg = ' || p_collected_tax_concat_seg,
2651 pg_msg_level_debug);
2652 arp_util.debug('lock_compare_cover: ' || 'p_comments = ' || p_comments,
2653 pg_msg_level_debug);
2654 arp_util.debug('lock_compare_cover: ' || 'p_account_set_flag = ' || p_account_set_flag,
2655 pg_msg_level_debug);
2656 arp_util.debug('lock_compare_cover: ' || 'p_latest_rec_flag = ' || p_latest_rec_flag,
2657 pg_msg_level_debug);
2658 arp_util.debug('lock_compare_cover: ' || 'p_ussgl_transaction_code = ' ||
2659 p_ussgl_transaction_code,
2660 pg_msg_level_debug);
2661 arp_util.debug('lock_compare_cover: ' || 'p_ussgl_trx_code_context = ' ||
2662 p_ussgl_trx_code_context,
2663 pg_msg_level_debug);
2664 arp_util.debug('lock_compare_cover: ' || 'p_attribute_category = ' || p_attribute_category,
2665 pg_msg_level_debug);
2666 arp_util.debug('lock_compare_cover: ' || 'p_attribute1 = ' || p_attribute1,
2667 pg_msg_level_debug);
2668 arp_util.debug('lock_compare_cover: ' || 'p_attribute2 = ' || p_attribute2,
2669 pg_msg_level_debug);
2670 arp_util.debug('lock_compare_cover: ' || 'p_attribute3 = ' || p_attribute3,
2671 pg_msg_level_debug);
2672 arp_util.debug('lock_compare_cover: ' || 'p_attribute4 = ' || p_attribute4,
2673 pg_msg_level_debug);
2674 arp_util.debug('lock_compare_cover: ' || 'p_attribute5 = ' || p_attribute5,
2675 pg_msg_level_debug);
2676 arp_util.debug('lock_compare_cover: ' || 'p_attribute6 = ' || p_attribute6,
2677 pg_msg_level_debug);
2678 arp_util.debug('lock_compare_cover: ' || 'p_attribute7 = ' || p_attribute7,
2679 pg_msg_level_debug);
2680 arp_util.debug('lock_compare_cover: ' || 'p_attribute8 = ' || p_attribute8,
2681 pg_msg_level_debug);
2682 arp_util.debug('lock_compare_cover: ' || 'p_attribute9 = ' || p_attribute9,
2683 pg_msg_level_debug);
2684 arp_util.debug('lock_compare_cover: ' || 'p_attribute10 = ' || p_attribute10,
2685 pg_msg_level_debug);
2686 arp_util.debug('lock_compare_cover: ' || 'p_attribute11 = ' || p_attribute11,
2687 pg_msg_level_debug);
2688 arp_util.debug('lock_compare_cover: ' || 'p_attribute12 = ' || p_attribute12,
2689 pg_msg_level_debug);
2690 arp_util.debug('lock_compare_cover: ' || 'p_attribute13 = ' || p_attribute13,
2691 pg_msg_level_debug);
2692 arp_util.debug('lock_compare_cover: ' || 'p_attribute14 = ' || p_attribute14,
2693 pg_msg_level_debug);
2694 arp_util.debug('lock_compare_cover: ' || 'p_attribute15 = ' || p_attribute15,
2695 pg_msg_level_debug);
2696 arp_util.debug('lock_compare_cover: ' || 'p_posting_control_id = ' || p_posting_control_id,
2697 pg_msg_level_debug);
2698 END IF;
2699
2700 RAISE;
2701
2702 END lock_compare_cover;
2703
2704 /*===========================================================================+
2705 | PROCEDURE |
2706 | fetch_p |
2707 | |
2708 | DESCRIPTION |
2709 | This procedure fetches a single row from ra_cust_trx_line_gl_dist |
2710 | into a variable specified as a parameter based on the table's primary |
2711 | key, cust_trx_line_gl_dist_id |
2712 | |
2713 | SCOPE - PUBLIC |
2714 | |
2715 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2716 | arp_util.debug |
2717 | |
2718 | ARGUMENTS : IN: |
2719 | p_cust_trx_line_gl_dist_id - identifies the record to fetch |
2720 | OUT: |
2721 | p_dist_rec - contains the fetched record |
2722 | |
2723 | RETURNS : NONE |
2724 | |
2725 | NOTES |
2726 | |
2727 | MODIFICATION HISTORY |
2728 | 09-JUN-95 Charlie Tomberg Created |
2729 | |
2730 +===========================================================================*/
2731
2732 PROCEDURE fetch_p( p_dist_rec OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype,
2733 p_cust_trx_line_gl_dist_id IN
2734 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type)
2735 IS
2736
2737 BEGIN
2738 arp_util.debug('arp_ctlgd_pkg.fetch_p()+');
2739
2740 SELECT *
2741 INTO p_dist_rec
2742 FROM ra_cust_trx_line_gl_dist
2743 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id;
2744
2745 arp_util.debug('arp_ctlgd_pkg.fetch_p()-');
2746
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 arp_util.debug( 'EXCEPTION: arp_ctlgd_pkg.fetch_p' );
2750 RAISE;
2751 END;
2752
2753
2754 /*===========================================================================+
2755 | PROCEDURE |
2756 | delete_p |
2757 | |
2758 | DESCRIPTION |
2759 | This procedure deletes the ra_cust_trx_line_gl_dist row identified |
2760 | by the p_cust_trx_line_gl_dist_id parameter. |
2761 | |
2762 | SCOPE - PUBLIC |
2763 | |
2764 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2765 | arp_util.debug |
2766 | |
2767 | ARGUMENTS : IN: |
2768 | p_cust_trx_line_gl_dist_id - identifies the rows to delete |
2769 | OUT: |
2770 | None |
2771 | |
2772 | RETURNS : NONE |
2773 | |
2774 | NOTES |
2775 | |
2776 | MODIFICATION HISTORY |
2777 | 09-JUN-95 Charlie Tomberg Created |
2778 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2779 | added processing calls for delete from |
2780 | ra_cust_trx_lines_gl_dist |
2781 | |
2782 +===========================================================================*/
2783
2784 procedure delete_p( p_cust_trx_line_gl_dist_id
2785 IN ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type)
2786 IS
2787
2788
2789 BEGIN
2790
2791
2792 arp_util.debug('arp_ctlgd_pkg.delete_p()+');
2793
2794 DELETE FROM ra_cust_trx_line_gl_dist
2795 WHERE cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id;
2796
2797 IF ( SQL%ROWCOUNT = 0 )
2798 THEN arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_p()');
2799 RAISE NO_DATA_FOUND;
2800 END IF;
2801
2802 /* call mrc api to delete */
2803 arp_standard.debug('calling mrc engine for insertion of gl dist data');
2804 ar_mrc_engine.maintain_mrc_data(
2805 p_event_mode => 'DELETE',
2806 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2807 p_mode => 'SINGLE',
2808 p_key_value => p_cust_trx_line_gl_dist_id);
2809
2810
2811 arp_util.debug('arp_ctlgd_pkg.delete_p()-');
2812
2813 EXCEPTION
2814 WHEN OTHERS THEN
2815 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_p()');
2816
2817 RAISE;
2818
2819 END;
2820
2821 /*===========================================================================+
2822 | PROCEDURE |
2823 | delete_f_ct_id |
2824 | |
2825 | DESCRIPTION |
2826 | This procedure deletes the ra_cust_trx_line_gl_dist rows identified |
2827 | by the p_customer_trx_id parameter. |
2828 | |
2829 | SCOPE - PUBLIC |
2830 | |
2831 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2832 | arp_util.debug |
2833 | |
2834 | ARGUMENTS : IN: |
2835 | p_customer_trx_id - identifies the rows to delete |
2836 | p_account_set_flag - value is used to restrict delete |
2837 | p_account_class - value is used to restrict delete |
2838 | OUT: |
2839 | None |
2840 | |
2841 | RETURNS : NONE |
2842 | |
2843 | NOTES |
2844 | |
2845 | MODIFICATION HISTORY |
2846 | 09-JUN-95 Charlie Tomberg Created |
2847 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2848 | added processing calls for delete from |
2849 | ra_cust_trx_lines_gl_dist |
2850 | |
2851 +===========================================================================*/
2852
2853 procedure delete_f_ct_id( p_customer_trx_id
2854 IN ra_customer_trx.customer_trx_id%type,
2855 p_account_set_flag
2856 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
2857 p_account_class
2858 IN ra_cust_trx_line_gl_dist.account_class%type)
2859 IS
2860
2861 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2862
2863 BEGIN
2864
2865 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_id()+');
2866
2867 DELETE FROM ra_cust_trx_line_gl_dist
2868 WHERE customer_trx_id = p_customer_trx_id
2869 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
2870 AND account_class = nvl(p_account_class, account_class)
2871 RETURNING cust_trx_line_gl_dist_id
2872 BULK COLLECT INTO l_gl_dist_key_value_list;
2873
2874 arp_standard.debug('calling mrc engine for insertion of gl dist data');
2875 ar_mrc_engine.maintain_mrc_data(
2876 p_event_mode => 'DELETE',
2877 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2878 p_mode => 'BATCH',
2879 p_key_value_list => l_gl_dist_key_value_list);
2880
2881
2882 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_id()-');
2883
2884 EXCEPTION
2885 WHEN OTHERS THEN
2886 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ct_id()');
2887
2888 RAISE;
2889
2890 END;
2891
2892 /*===========================================================================+
2893 | PROCEDURE |
2894 | delete_f_ctl_id |
2895 | |
2896 | DESCRIPTION |
2897 | This procedure deletes the ra_cust_trx_line_gl_dist rows identified |
2898 | by the p_customer_trx_line_id parameter. |
2899 | |
2900 | SCOPE - PUBLIC |
2901 | |
2902 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2903 | arp_util.debug |
2904 | |
2905 | ARGUMENTS : IN: |
2906 | p_customer_trx_line_id - identifies the rows to delete |
2907 | p_account_set_flag - value is used to restrict delete |
2908 | p_account_class - value is used to restrict delete |
2909 | OUT: |
2910 | None |
2911 | |
2912 | RETURNS : NONE |
2913 | |
2914 | NOTES |
2915 | |
2916 | MODIFICATION HISTORY |
2917 | 09-JUN-95 Charlie Tomberg Created |
2918 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2919 | added processing calls for delete from |
2920 | ra_cust_trx_lines_gl_dist |
2921 | |
2922 +===========================================================================*/
2923
2924 procedure delete_f_ctl_id( p_customer_trx_line_id
2925 IN ra_customer_trx_lines.customer_trx_line_id%type,
2926 p_account_set_flag
2927 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
2928 p_account_class
2929 IN ra_cust_trx_line_gl_dist.account_class%type)
2930 IS
2931
2932 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2933
2934 BEGIN
2935
2936
2937 arp_util.debug('arp_ctlgd_pkg.delete_f_ctl_id()+');
2938
2939 DELETE FROM ra_cust_trx_line_gl_dist
2940 WHERE customer_trx_line_id = p_customer_trx_line_id
2941 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
2942 AND account_class = nvl(p_account_class, account_class)
2943 RETURNING cust_trx_line_gl_dist_id
2944 BULK COLLECT INTO l_gl_dist_key_value_list;
2945
2946 arp_standard.debug('calling mrc engine for insertion of gl dist data');
2947 ar_mrc_engine.maintain_mrc_data(
2948 p_event_mode => 'DELETE',
2949 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
2950 p_mode => 'BATCH',
2951 p_key_value_list => l_gl_dist_key_value_list);
2952
2953 arp_util.debug('arp_ctlgd_pkg.delete_f_ctl_id()-');
2954
2955 EXCEPTION
2956 WHEN OTHERS THEN
2957 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ctl_id()');
2958
2959 RAISE;
2960
2961 END;
2962
2963 /*===========================================================================+
2964 | PROCEDURE |
2965 | delete_f_ctls_id |
2966 | |
2967 | DESCRIPTION |
2968 | This procedure deletes the ra_cust_trx_line_gl_dist rows identified |
2969 | by the p_cust_trx_line_salesrep_id parameter. |
2970 | |
2971 | SCOPE - PUBLIC |
2972 | |
2973 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2974 | arp_util.debug |
2975 | |
2976 | ARGUMENTS : IN: |
2977 | p_cust_trx_line_salesrep - identifies the rows to delete |
2978 | p_account_set_flag - value is used to restrict delete |
2979 | p_account_class - value is used to restrict delete |
2980 | OUT: |
2981 | None |
2982 | |
2983 | RETURNS : NONE |
2984 | |
2985 | NOTES |
2986 | |
2987 | MODIFICATION HISTORY |
2988 | 09-JUN-95 Charlie Tomberg Created |
2989 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
2990 | added processing calls for delete from |
2991 | ra_cust_trx_lines_gl_dist |
2992 | |
2993 +===========================================================================*/
2994
2995 procedure delete_f_ctls_id( p_cust_trx_line_salesrep_id
2996 IN ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type,
2997 p_account_set_flag
2998 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
2999 p_account_class
3000 IN ra_cust_trx_line_gl_dist.account_class%type)
3001 IS
3002
3003 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3004
3005 BEGIN
3006
3007 arp_util.debug('arp_ctlgd_pkg.delete_f_ctls_id()+');
3008
3009 DELETE FROM ra_cust_trx_line_gl_dist
3010 WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
3011 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
3012 AND account_class = nvl(p_account_class, account_class)
3013 RETURNING cust_trx_line_gl_dist_id
3014 BULK COLLECT INTO l_gl_dist_key_value_list;
3015
3016 arp_standard.debug('calling mrc engine for insertion of gl dist data');
3017 ar_mrc_engine.maintain_mrc_data(
3018 p_event_mode => 'DELETE',
3019 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3020 p_mode => 'BATCH',
3021 p_key_value_list => l_gl_dist_key_value_list);
3022
3023
3024 arp_util.debug('arp_ctlgd_pkg.delete_f_ctls_id()-');
3025
3026 EXCEPTION
3027 WHEN OTHERS THEN
3028 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ctls_id()');
3029
3030 RAISE;
3031
3032 END;
3033
3034 /*===========================================================================+
3035 | PROCEDURE |
3036 | delete_f_ct_ltctl_id_type |
3037 | |
3038 | DESCRIPTION |
3039 | This procedure deletes the ra_cust_trx_line_gl_dist rows corresponding |
3040 | to the child lines (incl. header freight line) identified by |
3041 | p_customer_trx_id, p_link_to_cust_trx_line_id and p_line_type |
3042 | |
3043 | SCOPE - PUBLIC |
3044 | |
3045 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3046 | arp_util.debug |
3047 | |
3048 | ARGUMENTS : IN: |
3049 | p_customer_trx_id - identifies the transaction |
3050 | p_link_to_cust_trx_line_id - identifies the parent line |
3051 | p_line_type - identifies the parent line |
3052 | type |
3053 | p_account_set_flag - value is used to restrict delete |
3054 | p_account_class - value is used to restrict delete |
3055 | OUT: |
3056 | None |
3057 | |
3058 | RETURNS : NONE |
3059 | |
3060 | NOTES |
3061 | |
3062 | MODIFICATION HISTORY |
3063 | 14-SEP-95 Subash Chadalavada Created |
3064 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3065 | added processing calls for delete from |
3066 | ra_cust_trx_lines_gl_dist |
3067 +===========================================================================*/
3068
3069 PROCEDURE delete_f_ct_ltctl_id_type(
3070 p_customer_trx_id IN
3071 ra_customer_trx.customer_trx_id%type,
3072 p_link_to_cust_trx_line_id IN
3073 ra_customer_trx_lines.link_to_cust_trx_line_id%type,
3074 p_line_type IN
3075 ra_customer_trx_lines.line_type%type,
3076 p_account_set_flag IN
3077 ra_cust_trx_line_gl_dist.account_set_flag%type,
3078 p_account_class IN
3079 ra_cust_trx_line_gl_dist.account_class%type)
3080 IS
3081
3082 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3083
3084 BEGIN
3085
3086 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_ltctl_id_type()+');
3087
3088 DELETE FROM ra_cust_trx_line_gl_dist
3089 WHERE customer_trx_id = p_customer_trx_id
3090 AND customer_trx_line_id in
3091 ( SELECT customer_trx_line_id
3092 FROM ra_customer_trx_lines
3093 WHERE customer_trx_id = p_customer_trx_id
3094 AND decode(p_link_to_cust_trx_line_id,
3095 null, -99,
3096 link_to_cust_trx_line_id) =
3097 nvl(p_link_to_cust_trx_line_id, -99)
3098 AND line_type = nvl(p_line_type, line_type)
3099 )
3100 AND account_set_flag = nvl(p_account_set_flag, account_set_flag)
3101 AND account_class = nvl(p_account_class, account_class)
3102 RETURNING cust_trx_line_gl_dist_id
3103 BULK COLLECT INTO l_gl_dist_key_value_list;
3104
3105 arp_standard.debug('calling mrc engine for insertion of gl dist data');
3106 ar_mrc_engine.maintain_mrc_data(
3107 p_event_mode => 'DELETE',
3108 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3109 p_mode => 'BATCH',
3110 p_key_value_list => l_gl_dist_key_value_list);
3111
3112
3113 arp_util.debug('arp_ctlgd_pkg.delete_f_ct_ltctl_id_type()-');
3114
3115 EXCEPTION
3116 WHEN OTHERS THEN
3117 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.delete_f_ct_ltctl_id_type()');
3118
3119 RAISE;
3120
3121 END;
3122
3123
3124 /*===========================================================================+
3125 | PROCEDURE |
3126 | update_p |
3127 | |
3128 | DESCRIPTION |
3129 | This procedure updates the ra_cust_trx_line_gl_dist row identified |
3130 | by the p_cust_trx_line_gl_dist_id parameter. |
3131 | |
3132 | SCOPE - PUBLIC |
3133 | |
3134 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3135 | arp_util.debug |
3136 | |
3137 | ARGUMENTS : IN: |
3138 | p_cust_trx_line_gl_dist_id - identifies the row to update |
3139 | p_dist_rec - contains the new column values |
3140 | p_exchange_rate |
3141 | p_currency_code |
3142 | p_precision |
3143 | p_mau |
3144 | OUT: |
3145 | None |
3146 | |
3147 | RETURNS : NONE |
3148 | |
3149 | NOTES |
3150 | set_to_dummy must be called before the values in p_dist_rec are |
3151 | changed and this function is called. |
3152 | |
3153 | MODIFICATION HISTORY |
3154 | 09-JUN-95 Charlie Tomberg Created |
3155 | |
3156 +===========================================================================*/
3157
3158 PROCEDURE update_p( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3159 p_cust_trx_line_gl_dist_id IN
3160 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
3161 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3162 DEFAULT 1,
3163 p_currency_code IN fnd_currencies.currency_code%type
3164 DEFAULT null,
3165 p_precision IN fnd_currencies.precision%type
3166 DEFAULT null,
3167 p_mau IN
3168 fnd_currencies.minimum_accountable_unit%type
3169 DEFAULT null)
3170 IS
3171
3172 l_where varchar2(500);
3173
3174 BEGIN
3175
3176 arp_util.debug('arp_ctlgd_pkg.update_p()+ ' ||
3177 to_char(sysdate, 'HH:MI:SS'));
3178
3179 l_where := ' WHERE cust_trx_line_gl_dist_id = :where_1 ' ||
3180 'AND account_set_flag = nvl(:where_account_set_flag, '||
3181 'account_set_flag) ' ||
3182 'AND account_class = nvl(:where_account_class, account_class)';
3183
3184
3185 arp_ctlgd_pkg.generic_update( pg_cursor1,
3186 l_where,
3187 p_cust_trx_line_gl_dist_id,
3188 null,
3189 null,
3190 p_exchange_rate,
3191 p_currency_code,
3192 p_precision,
3193 p_mau,
3194 p_dist_rec);
3195
3196 arp_util.debug('arp_ctlgd_pkg.update_p()- ' ||
3197 to_char(sysdate, 'HH:MI:SS'));
3198
3199
3200 EXCEPTION
3201 WHEN OTHERS THEN
3202 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_p()');
3203 RAISE;
3204 END;
3205
3206
3207 /*===========================================================================+
3208 | PROCEDURE |
3209 | update_f_ct_id |
3210 | |
3211 | DESCRIPTION |
3212 | This procedure updates the ra_cust_trx_line_gl_dist rows identified |
3213 | by the p_customer_trx_id parameter. |
3214 | |
3215 | SCOPE - PUBLIC |
3216 | |
3217 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3218 | arp_util.debug |
3219 | |
3220 | ARGUMENTS : IN: |
3221 | p_customer_trx_id - identifies the rows to update |
3222 | p_dist_rec - contains the new column values |
3223 | p_account_set_flag - value is used to restrict update |
3224 | p_account_class - value is used to restrict update |
3225 | p_exchange_rate |
3226 | p_currency_code |
3227 | p_precision |
3228 | p_mau |
3229 | OUT: |
3230 | None |
3231 | |
3232 | RETURNS : NONE |
3233 | |
3234 | NOTES |
3235 | set_to_dummy must be called before the values in p_dist_rec are |
3236 | changed and this function is called. |
3237 | |
3238 | MODIFICATION HISTORY |
3239 | 09-JUN-95 Charlie Tomberg Created |
3240 | |
3241 +===========================================================================*/
3242
3243 PROCEDURE update_f_ct_id( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3244 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
3245 p_account_set_flag
3246 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3247 p_account_class
3248 IN ra_cust_trx_line_gl_dist.account_class%type,
3249 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3250 DEFAULT 1,
3251 p_currency_code IN fnd_currencies.currency_code%type
3252 DEFAULT null,
3253 p_precision IN fnd_currencies.precision%type
3254 DEFAULT null,
3255 p_mau IN
3256 fnd_currencies.minimum_accountable_unit%type
3257 DEFAULT null)
3258 IS
3259
3260 l_where varchar2(500);
3261
3262 BEGIN
3263
3264 arp_util.debug('arp_ctlgd_pkg.update_f_ct_id()+ ' ||
3265 to_char(sysdate, 'HH:MI:SS'));
3266
3267 l_where := ' WHERE customer_trx_id = :where_1 ' ||
3268 'AND account_set_flag = nvl(:where_account_set_flag, '||
3269 'account_set_flag) ' ||
3270 'AND account_class = nvl(:where_account_class, account_class)';
3271
3272 arp_ctlgd_pkg.generic_update( pg_cursor2,
3273 l_where,
3274 p_customer_trx_id,
3275 p_account_set_flag,
3276 p_account_class,
3277 p_exchange_rate,
3278 p_currency_code,
3279 p_precision,
3280 p_mau,
3281 p_dist_rec);
3282
3283 arp_util.debug('arp_ctlgd_pkg.update_f_ct_id()- ' ||
3284 to_char(sysdate, 'HH:MI:SS'));
3285
3286
3287 EXCEPTION
3288 WHEN OTHERS THEN
3289 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_f_ct_id()');
3290 RAISE;
3291 END;
3292
3293
3294 /*===========================================================================+
3295 | PROCEDURE |
3296 | update_f_ctl_id |
3297 | |
3298 | DESCRIPTION |
3299 | This procedure updates the ra_cust_trx_line_gl_dist rows identified |
3300 | by the p_customer_trx_line_id parameter. |
3301 | |
3302 | SCOPE - PUBLIC |
3303 | |
3304 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3305 | arp_util.debug |
3306 | |
3307 | ARGUMENTS : IN: |
3308 | p_customer_trx_line_id - identifies the rows to update |
3309 | p_dist_rec - contains the new column values |
3310 | p_account_set_flag - value is used to restrict update |
3311 | p_account_class - value is used to restrict update |
3312 | p_exchange_rate |
3313 | p_currency_code |
3314 | p_precision |
3315 | p_mau |
3316 | OUT: |
3317 | None |
3318 | |
3319 | RETURNS : NONE |
3320 | |
3321 | NOTES |
3322 | set_to_dummy must be called before the values in p_dist_rec are |
3323 | changed and this function is called. |
3324 | |
3325 | MODIFICATION HISTORY |
3326 | 09-JUN-95 Charlie Tomberg Created |
3327 | |
3328 +===========================================================================*/
3329
3330 PROCEDURE update_f_ctl_id( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3331 p_customer_trx_line_id IN
3332 ra_customer_trx_lines.customer_trx_line_id%type,
3333 p_account_set_flag
3334 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3335 p_account_class
3336 IN ra_cust_trx_line_gl_dist.account_class%type,
3337 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3338 DEFAULT 1,
3339 p_currency_code IN fnd_currencies.currency_code%type
3340 DEFAULT null,
3341 p_precision IN fnd_currencies.precision%type
3342 DEFAULT null,
3343 p_mau IN
3344 fnd_currencies.minimum_accountable_unit%type
3345 DEFAULT null)
3346 IS
3347
3348 l_where varchar2(500);
3349
3350 BEGIN
3351
3352 arp_util.debug('arp_ctlgd_pkg.update_f_ctl_id()+ ' ||
3353 to_char(sysdate, 'HH:MI:SS'));
3354
3355 l_where := ' WHERE customer_trx_line_id = :where_1 ' ||
3356 'AND account_set_flag = nvl(:where_account_set_flag, '||
3357 'account_set_flag) ' ||
3358 'AND account_class = nvl(:where_account_class, account_class)';
3359
3360
3361 arp_ctlgd_pkg.generic_update( pg_cursor3,
3362 l_where,
3363 p_customer_trx_line_id,
3364 p_account_set_flag,
3365 p_account_class,
3366 p_exchange_rate,
3367 p_currency_code,
3368 p_precision,
3369 p_mau,
3370 p_dist_rec);
3371
3372 arp_util.debug('arp_ctlgd_pkg.update_f_ctl_id()- ' ||
3373 to_char(sysdate, 'HH:MI:SS'));
3374
3375
3376 EXCEPTION
3377 WHEN OTHERS THEN
3378 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_f_ctl_id()');
3379 RAISE;
3380 END;
3381
3382 /*===========================================================================+
3383 | PROCEDURE |
3384 | update_f_ctls_id |
3385 | |
3386 | DESCRIPTION |
3387 | This procedure updates the ra_cust_trx_line_gl_dist rows identified |
3388 | by the p_cust_trx_line_salesrep_id parameter. |
3389 | |
3390 | SCOPE - PUBLIC |
3391 | |
3392 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3393 | arp_util.debug |
3394 | |
3395 | ARGUMENTS : IN: |
3396 | p_cust_trx_line_salesrep_id - identifies the rows to update |
3397 | p_dist_rec - contains the new column values |
3398 | p_account_set_flag - value is used to restrict update |
3399 | p_account_class - value is used to restrict update |
3400 | p_exchange_rate |
3401 | p_currency_code |
3402 | p_precision |
3403 | p_mau |
3404 | OUT: |
3405 | None |
3406 | |
3407 | RETURNS : NONE |
3408 | |
3409 | NOTES |
3410 | set_to_dummy must be called before the values in p_dist_rec are |
3411 | changed and this function is called. |
3412 | |
3413 | MODIFICATION HISTORY |
3414 | 09-JUN-95 Charlie Tomberg Created |
3415 | |
3416 +===========================================================================*/
3417
3418 PROCEDURE update_f_ctls_id( p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3419 p_cust_trx_line_salesrep_id IN
3420 ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type,
3421 p_account_set_flag
3422 IN ra_cust_trx_line_gl_dist.account_set_flag%type,
3423 p_account_class
3424 IN ra_cust_trx_line_gl_dist.account_class%type,
3425 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3426 DEFAULT 1,
3427 p_currency_code IN fnd_currencies.currency_code%type
3428 DEFAULT null,
3429 p_precision IN fnd_currencies.precision%type
3430 DEFAULT null,
3431 p_mau IN
3432 fnd_currencies.minimum_accountable_unit%type
3433 DEFAULT null)
3434 IS
3435
3436 l_where varchar2(500);
3437
3438 BEGIN
3439
3440 arp_util.debug('arp_ctlgd_pkg.update_f_ctls_id()+ ' ||
3441 to_char(sysdate, 'HH:MI:SS'));
3442
3443 l_where := ' WHERE cust_trx_line_salesrep_id = :where_1 ' ||
3444 'AND account_set_flag = nvl(:where_account_set_flag, '||
3445 'account_set_flag) ' ||
3446 'AND account_class = nvl(:where_account_class, account_class)';
3447
3448 arp_ctlgd_pkg.generic_update( pg_cursor4,
3449 l_where,
3450 p_cust_trx_line_salesrep_id,
3451 p_account_set_flag,
3452 p_account_class,
3453 p_exchange_rate,
3454 p_currency_code,
3455 p_precision,
3456 p_mau,
3457 p_dist_rec);
3458
3459 arp_util.debug('arp_ctlgd_pkg.update_f_ctls_id()- ' ||
3460 to_char(sysdate, 'HH:MI:SS'));
3461
3462
3463 EXCEPTION
3464 WHEN OTHERS THEN
3465 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_f_ctls_id()');
3466 RAISE;
3467 END;
3468
3469
3470 /*===========================================================================+
3471 | PROCEDURE |
3472 | update_acctd_amount |
3473 | |
3474 | DESCRIPTION |
3475 | |
3476 | Updates ra_cust_trx_line_gl_dist.acctd_amount for rows identified by |
3477 | the p_customer_trx_id parameter. |
3478 | |
3479 | SCOPE - PUBLIC |
3480 | |
3481 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3482 | arp_util.debug |
3483 | |
3484 | ARGUMENTS : IN: |
3485 | p_customer_trx_id - required |
3486 | p_base_curr_code - required |
3487 | p_exchange_rate - required |
3488 | p_base_precision - optional |
3489 | p_base_min_acc_unit - optional |
3490 | OUT: |
3491 | RETURNS : NONE |
3492 | |
3493 | NOTES |
3494 | If p_base_precision and p_base_min_acc_unit are null, |
3495 | arpcurr.functional_amount figures out NOCOPY what they are. |
3496 | |
3497 | MODIFICATION HISTORY |
3498 | 21-JUL-95 Martin Johnson Created |
3499 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3500 | added calls for processing updates to |
3501 | ra_cust_trx_line_gl_dist. |
3502 | 31-AUG-04 M Raymond Added IF condition to only do MRC
3503 | update if rows were updated in primary
3504 +===========================================================================*/
3505
3506 PROCEDURE update_acctd_amount(p_customer_trx_id IN number,
3507 p_base_curr_code IN
3508 fnd_currencies.currency_code%type,
3509 p_exchange_rate IN
3510 ra_customer_trx.exchange_rate%type,
3511 p_base_precision IN
3512 fnd_currencies.precision%type
3513 default null,
3514 p_base_min_acc_unit IN
3515 fnd_currencies.minimum_accountable_unit%type
3516 default null)
3517 IS
3518 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3519
3520 BEGIN
3521
3522 arp_util.debug('arp_ctlgd_pkg.update_acctd_amount()+');
3523
3524 update ra_cust_trx_line_gl_dist
3525 set last_updated_by = pg_user_id,
3526 last_update_date = sysdate,
3527 last_update_login = pg_login_id,
3528 acctd_amount = arpcurr.functional_amount(
3529 amount,
3530 p_base_curr_code,
3531 p_exchange_rate,
3532 p_base_precision,
3533 p_base_min_acc_unit)
3534 where customer_trx_id = p_customer_trx_id
3535 and account_set_flag = 'N'
3536 and account_class <> 'REC'
3537 RETURNING cust_trx_line_gl_dist_id
3538 BULK COLLECT INTO l_gl_dist_key_value_list;
3539
3540 /* Bug 3858542 - Added IF condition to prevent failures in
3541 MRC code when no rows will be updated */
3542 IF (SQL%ROWCOUNT > 0 )
3543 THEN
3544 arp_standard.debug('calling mrc engine for update of gl dist data');
3545 ar_mrc_engine.maintain_mrc_data(
3546 p_event_mode => 'UPDATE',
3547 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3548 p_mode => 'BATCH',
3549 p_key_value_list => l_gl_dist_key_value_list);
3550 END IF;
3551
3552 arp_util.debug('arp_ctlgd_pkg.update_acctd_amount()-');
3553
3554 EXCEPTION
3555 WHEN NO_DATA_FOUND THEN
3556 arp_util.debug('arp_ctlgd_pkg.update_acctd_amount()-');
3557 WHEN OTHERS THEN
3558 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_acctd_amount()');
3559 RAISE;
3560 END;
3561
3562 /*===========================================================================+
3563 | PROCEDURE |
3564 | update_amount_f_ctl_id |
3565 | |
3566 | DESCRIPTION |
3567 | |
3568 | Updates ra_cust_trx_line_gl_dist amount columns for rows identified by |
3569 | the p_customer_trx_id parameter. |
3570 | |
3571 | SCOPE - PUBLIC |
3572 | |
3573 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3574 | arp_util.debug |
3575 | |
3576 | ARGUMENTS : IN: |
3577 | p_customer_trx_id - required |
3578 | p_line_amount - required |
3579 | p_base_curr_code - required |
3580 | p_exchange_rate - required |
3581 | p_base_precision - optional |
3582 | p_base_min_acc_unit - optional |
3583 | p_foreign_currency - required |
3584 | OUT: |
3585 | RETURNS : NONE |
3586 | |
3587 | NOTES |
3588 | If p_base_precision and p_base_min_acc_unit are null, |
3589 | arpcurr.functional_amount figures out NOCOPY what they are. |
3590 | |
3591 | MODIFICATION HISTORY |
3592 | 24-JUL-95 Martin Johnson Created |
3593 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3594 | added calls for processing updates to |
3595 | ra_cust_trx_line_gl_dist. |
3596 | |
3597 +===========================================================================*/
3598
3599 PROCEDURE update_amount_f_ctl_id(p_customer_trx_line_id IN
3600 ra_customer_trx_lines.customer_trx_line_id%type,
3601 p_line_amount IN
3602 ra_customer_trx_lines.extended_amount%type,
3603 p_foreign_currency_code IN
3604 fnd_currencies.currency_code%type,
3605 p_base_curr_code IN
3606 fnd_currencies.currency_code%type,
3607 p_exchange_rate IN
3608 ra_customer_trx.exchange_rate%type,
3609 p_base_precision IN
3610 fnd_currencies.precision%type
3611 default null,
3612 p_base_min_acc_unit IN
3613 fnd_currencies.minimum_accountable_unit%type
3614 default null)
3615 IS
3616 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
3617
3618 BEGIN
3619
3620 arp_util.debug('arp_ctlgd_pkg.update_amount_f_ctl_id()+');
3621
3622 UPDATE ra_cust_trx_line_gl_dist
3623 SET last_updated_by = pg_user_id,
3624 last_update_date = sysdate,
3625 last_update_login = pg_login_id,
3626 amount = arpcurr.CurrRound(
3627 p_line_amount *
3628 ( percent / 100 ),
3629 p_foreign_currency_code
3630 ),
3631 acctd_amount = arpcurr.functional_amount(
3632 arpcurr.CurrRound(
3633 p_line_amount *
3634 ( percent / 100 ),
3635 p_foreign_currency_code
3636 ),
3637 p_base_curr_code,
3638 p_exchange_rate,
3639 p_base_precision,
3640 p_base_min_acc_unit)
3641 WHERE customer_trx_line_id = p_customer_trx_line_id
3642 AND account_set_flag = 'N'
3643 RETURNING cust_trx_line_gl_dist_id
3644 BULK COLLECT INTO l_gl_dist_key_value_list;
3645 IF (SQL%ROWCOUNT > 0 ) then
3646 /* Used arp_global.request_id for bug3620556 */
3647 IF arp_global.request_id is NULL then
3648 arp_standard.debug('calling mrc engine for update of gl dist data');
3649 ar_mrc_engine.maintain_mrc_data(
3650 p_event_mode => 'UPDATE',
3651 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3652 p_mode => 'BATCH',
3653 p_key_value_list => l_gl_dist_key_value_list);
3654 END IF;
3655 END IF;
3656 arp_util.debug('arp_ctlgd_pkg.update_amount_f_ctl_id()-');
3657
3658 EXCEPTION
3659 WHEN NO_DATA_FOUND THEN
3660 arp_util.debug('arp_ctlgd_pkg.update_amount_f_ctl_id()-');
3661 WHEN OTHERS THEN
3662 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.update_amount_f_ctl_id()');
3663 RAISE;
3664 END;
3665
3666
3667 /*===========================================================================+
3668 | PROCEDURE |
3669 | insert_p |
3670 | |
3671 | DESCRIPTION |
3672 | This procedure inserts a row into ra_cust_trx_line_gl_dists that |
3673 | contains the column values specified in the p_dist_rec parameter. |
3674 | |
3675 | SCOPE - PUBLIC |
3676 | |
3677 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3678 | arpcurr.functional_amount |
3679 | arp_util.debug |
3680 | arp_global.set_of_books_id |
3681 | |
3682 | ARGUMENTS : IN: |
3683 | p_dist_rec - contains the new column values |
3684 | OUT: |
3685 | p_cust_trx_line_gl_dist_id - unique ID of the new row |
3686 | p_exchange_rate |
3687 | p_currency_code |
3688 | p_precision |
3689 | p_mau |
3690 | |
3691 | RETURNS : NONE |
3692 | |
3693 | NOTES |
3694 | |
3695 | MODIFICATION HISTORY |
3696 | 09-JUN-95 Charlie Tomberg Created |
3697 | 10-OCT-95 Martin Johnson Always populate posting_control_id |
3698 | with -3. |
3699 | Populate latest_rec_flag with 'Y' |
3700 | if account_class is REC, otherwise |
3701 | populate with null. |
3702 | 14-Aug-02 Debbie Jancis Modified for MRC trigger replacement |
3703 | added calls for processing inserts to |
3704 | ra_cust_trx_line_gl_dist. |
3705 | |
3706 +===========================================================================*/
3707
3708 PROCEDURE insert_p(
3709 p_dist_rec IN ra_cust_trx_line_gl_dist%rowtype,
3710 p_cust_trx_line_gl_dist_id
3711 OUT NOCOPY ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type,
3712 p_exchange_rate IN ra_customer_trx.exchange_rate%type
3713 DEFAULT 1,
3714 p_currency_code IN fnd_currencies.currency_code%type
3715 DEFAULT null,
3716 p_precision IN fnd_currencies.precision%type
3717 DEFAULT null,
3718 p_mau IN fnd_currencies.minimum_accountable_unit%type
3719 DEFAULT null
3720 ) IS
3721
3722
3723 l_cust_trx_line_gl_dist_id
3724 ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%type;
3725
3726
3727 BEGIN
3728
3729 arp_util.debug('arp_ctlgd_pkg.insert_p()+');
3730
3731 p_cust_trx_line_gl_dist_id := '';
3732
3733 /*---------------------------*
3734 | Get the unique identifier |
3735 *---------------------------*/
3736
3737 SELECT RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL
3738 INTO l_cust_trx_line_gl_dist_id
3739 FROM DUAL;
3740
3741
3742 /*-------------------*
3743 | Insert the record |
3744 *-------------------*/
3745
3746 INSERT INTO ra_cust_trx_line_gl_dist
3747 (
3748 cust_trx_line_gl_dist_id,
3749 customer_trx_id,
3750 customer_trx_line_id,
3751 cust_trx_line_salesrep_id,
3752 account_class,
3753 percent,
3754 amount,
3755 acctd_amount,
3756 gl_date,
3757 original_gl_date,
3758 gl_posted_date,
3759 code_combination_id,
3760 concatenated_segments,
3761 collected_tax_ccid,
3762 collected_tax_concat_seg,
3763 comments,
3764 account_set_flag,
3765 latest_rec_flag,
3766 rec_offset_flag,
3767 ussgl_transaction_code,
3768 ussgl_transaction_code_context,
3769 attribute_category,
3770 attribute1,
3771 attribute2,
3772 attribute3,
3773 attribute4,
3774 attribute5,
3775 attribute6,
3776 attribute7,
3777 attribute8,
3778 attribute9,
3779 attribute10,
3780 attribute11,
3781 attribute12,
3782 attribute13,
3783 attribute14,
3784 attribute15,
3785 set_of_books_id,
3786 posting_control_id,
3787 last_update_date,
3788 last_updated_by,
3789 creation_date,
3790 created_by,
3791 last_update_login,
3792 program_application_id,
3793 program_id,
3794 program_update_date,
3795 org_id,
3796 rounding_correction_flag
3797 )
3798 VALUES
3799 (
3800 l_cust_trx_line_gl_dist_id,
3801 p_dist_rec.customer_trx_id,
3802 p_dist_rec.customer_trx_line_id,
3803 p_dist_rec.cust_trx_line_salesrep_id,
3804 p_dist_rec.account_class,
3805 p_dist_rec.percent,
3806 p_dist_rec.amount,
3807 nvl(
3808 p_dist_rec.acctd_amount,
3809 decode(p_dist_rec.amount,
3810 null, to_number(null),
3811 arpcurr.functional_amount(
3812 p_dist_rec.amount,
3813 p_currency_code,
3814 p_exchange_rate,
3815 p_precision,
3816 p_mau
3817 )
3818 )
3819 ),
3820 p_dist_rec.gl_date,
3821 p_dist_rec.original_gl_date,
3822 p_dist_rec.gl_posted_date,
3823 p_dist_rec.code_combination_id,
3824 p_dist_rec.concatenated_segments,
3825 p_dist_rec.collected_tax_ccid,
3826 p_dist_rec.collected_tax_concat_seg,
3827 p_dist_rec.comments,
3828 p_dist_rec.account_set_flag,
3829 decode(p_dist_rec.account_class,
3830 'REC', 'Y',
3831 null),
3832 p_dist_rec.rec_offset_flag,
3833 p_dist_rec.ussgl_transaction_code,
3834 p_dist_rec.ussgl_transaction_code_context,
3835 p_dist_rec.attribute_category,
3836 p_dist_rec.attribute1,
3837 p_dist_rec.attribute2,
3838 p_dist_rec.attribute3,
3839 p_dist_rec.attribute4,
3840 p_dist_rec.attribute5,
3841 p_dist_rec.attribute6,
3842 p_dist_rec.attribute7,
3843 p_dist_rec.attribute8,
3844 p_dist_rec.attribute9,
3845 p_dist_rec.attribute10,
3846 p_dist_rec.attribute11,
3847 p_dist_rec.attribute12,
3848 p_dist_rec.attribute13,
3849 p_dist_rec.attribute14,
3850 p_dist_rec.attribute15,
3851 arp_global.set_of_books_id, /* set_of_books_id */
3852 -3, /* posting_control_id */
3853 sysdate, /*last_update_date */
3854 pg_user_id, /* last_updated_by */
3855 sysdate, /* creation_date */
3856 pg_user_id, /* created_by */
3857 nvl(pg_conc_login_id,
3858 pg_login_id), /* last_update_login */
3859 pg_prog_appl_id, /* program_application_id */
3860 pg_conc_program_id, /* program_id */
3861 sysdate, /* program_update_date */
3862 arp_standard.sysparm.org_id, /* SSA changes anuj */
3863 p_dist_rec.rounding_correction_flag
3864 );
3865
3866 p_cust_trx_line_gl_dist_id := l_cust_trx_line_gl_dist_id;
3867
3868 arp_standard.debug('calling mrc engine for INSERT of gl dist data');
3869 ar_mrc_engine.maintain_mrc_data(
3870 p_event_mode => 'INSERT',
3871 p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
3872 p_mode => 'SINGLE',
3873 p_key_value => p_cust_trx_line_gl_dist_id);
3874
3875 arp_util.debug('arp_ctlgd_pkg.insert_p()-');
3876
3877 EXCEPTION
3878 WHEN OTHERS THEN
3879 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.insert_p()');
3880 RAISE;
3881 END;
3882
3883 /*===========================================================================+
3884 | PROCEDURE |
3885 | select_summary |
3886 | |
3887 | DESCRIPTION |
3888 | Selects the total revenue percent and amount for a given transaction |
3889 | or line. This procedure is used by the forms running totals mechanism. |
3890 | |
3891 | SCOPE - PUBLIC |
3892 | |
3893 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3894 | arp_util.debug |
3895 | |
3896 | ARGUMENTS : IN: |
3897 | p_customer_trx_id |
3898 | p_customer_trx_line_id |
3899 | p_cust_trx_line_salesrep_id |
3900 | p_mode - the code of the alt. region displayed |
3901 | p_account_set_flag |
3902 | OUT: |
3903 | None |
3904 | IN/ OUT: |
3905 | p_amt_total |
3906 | p_amt_total_rtot_db |
3907 | p_pct_total |
3908 | p_pct_total_rtot_db |
3909 | p_pct_rev_total |
3910 | p_pct_rev_total_rtot_db |
3911 | p_pct_offset_total |
3912 | p_pct_offset_total_rtot_db |
3913 | p_pct_suspense_total |
3914 | p_pct_suspense_total_rtot_db |
3915 | |
3916 | RETURNS : NONE |
3917 | |
3918 | NOTES |
3919 | |
3920 | MODIFICATION HISTORY |
3921 | 17-OCT-95 Martin Johnson Created |
3922 | 20-OCT-95 Martin Johnson Added parameters: |
3923 | p_account_set_flag |
3924 | p_pct_rev_total |
3925 | p_pct_rev_total_rtot_db |
3926 | p_pct_offset_total |
3927 | p_pct_offset_total_rtot_db |
3928 | p_pct_suspense_total |
3929 | p_pct_suspense_total_rtot_db |
3930 | |
3931 +===========================================================================*/
3932
3933 PROCEDURE select_summary(
3934 p_customer_trx_id IN number,
3935 p_customer_trx_line_id IN number,
3936 p_cust_trx_line_salesrep_id IN number,
3937 p_mode IN varchar2,
3938 p_account_set_flag IN varchar2,
3939 p_amt_total IN OUT NOCOPY number,
3940 p_amt_total_rtot_db IN OUT NOCOPY number,
3941 p_pct_total IN OUT NOCOPY number,
3942 p_pct_total_rtot_db IN OUT NOCOPY number,
3943 p_pct_rev_total IN OUT NOCOPY number,
3944 p_pct_rev_total_rtot_db IN OUT NOCOPY number,
3945 p_pct_offset_total IN OUT NOCOPY number,
3946 p_pct_offset_total_rtot_db IN OUT NOCOPY number,
3947 p_pct_suspense_total IN OUT NOCOPY number,
3948 p_pct_suspense_total_rtot_db IN OUT NOCOPY number ) IS
3949
3950 BEGIN
3951
3952 IF PG_DEBUG in ('Y', 'C') THEN
3953 arp_util.debug('arp_ctlgd_pkg.select_summary()+');
3954 END IF;
3955
3956 SELECT NVL( SUM( NVL(amount, 0) ), 0),
3957 NVL( SUM( NVL(amount, 0) ), 0),
3958 NVL( SUM( NVL(percent, 0) ), 0),
3959 NVL( SUM( NVL(percent, 0) ), 0),
3960 NVL( SUM( DECODE(account_class,
3961 'REV', NVL(percent, 0),
3962 0) ), 0),
3963 NVL( SUM( DECODE(account_class,
3964 'REV', NVL(percent, 0),
3965 0) ), 0),
3966 NVL( SUM( DECODE(account_class,
3967 'UNEARN', NVL(percent, 0),
3968 'UNBILL', NVL(percent, 0),
3969 0) ), 0),
3970 NVL( SUM( DECODE(account_class,
3971 'UNEARN', NVL(percent, 0),
3972 'UNBILL', NVL(percent, 0),
3973 0) ), 0),
3974 NVL( SUM( DECODE(account_class,
3975 'SUSPENSE', NVL(percent, 0),
3976 0) ), 0),
3977 NVL( SUM( DECODE(account_class,
3978 'SUSPENSE', NVL(percent, 0),
3979 0) ), 0)
3980 INTO p_amt_total,
3981 p_amt_total_rtot_db,
3982 p_pct_total,
3983 p_pct_total_rtot_db,
3984 p_pct_rev_total,
3985 p_pct_rev_total_rtot_db,
3986 p_pct_offset_total,
3987 p_pct_offset_total_rtot_db,
3988 p_pct_suspense_total,
3989 p_pct_suspense_total_rtot_db
3990 FROM ra_cust_trx_line_gl_dist
3991 WHERE customer_trx_id = p_customer_trx_id
3992 AND customer_trx_line_id =
3993 DECODE(p_mode,
3994 'TACC_ACC_ASSGN_SREP', customer_trx_line_id,
3995 'TACC_ACC_SETS_SREP', customer_trx_line_id,
3996 p_customer_trx_line_id )
3997 AND NVL( cust_trx_line_salesrep_id, -10) =
3998 DECODE(p_mode,
3999 'TACC_ACC_ASSGN_SREP', p_cust_trx_line_salesrep_id,
4000 'TACC_ACC_SETS_SREP', p_cust_trx_line_salesrep_id,
4001 NVL( cust_trx_line_salesrep_id, -10) )
4002 AND account_set_flag = p_account_set_flag;
4003
4004 IF PG_DEBUG in ('Y', 'C') THEN
4005 arp_util.debug('arp_ctlgd_pkg.select_summary()-');
4006 END IF;
4007
4008 EXCEPTION
4009 WHEN OTHERS THEN
4010 IF PG_DEBUG in ('Y', 'C') THEN
4011 arp_util.debug('EXCEPTION: arp_ctlgd_pkg.select_summary()');
4012 END IF;
4013 RAISE;
4014
4015 END select_summary;
4016
4017 /*---------------------------------------------+
4018 | Package initialization section. |
4019 | Sets WHO column variables for later use. |
4020 +---------------------------------------------*/
4021
4022 BEGIN
4023
4024 pg_user_id := fnd_global.user_id;
4025 pg_conc_login_id := fnd_global.conc_login_id;
4026 pg_login_id := fnd_global.login_id;
4027 pg_prog_appl_id := fnd_global.prog_appl_id;
4028 pg_conc_program_id := fnd_global.conc_program_id;
4029
4030 pg_msg_level_debug := arp_global.MSG_LEVEL_DEBUG;
4031
4032 END ARP_CTLGD_PKG;