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