[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dummy constants for use in update and lock operations |
+--------------------------------------------------------*/
AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
| Package global variables to hold the parsed update cursors. |
| This allows the cursors to be reused without being reparsed. |
+---------------------------------------------------------------*/
pg_cursor1 integer := '';
| Erase foreign key references to the salescredit being deleted: |
| ra_cust_trx_line_salesreps.prev_cust_trx_line_salesrep_id |
| ra_cust_trx_line_gl_dist.cust_trx_line_salesrep_id |
+------------------------------------------------------------------*/
/*-------------------------------------------------------------------+
| Erase ra_cust_trx_line_salesreps.prev_cust_trx_line_salesrep_id |
| and erase ra_cust_trx_line_gl_dist.cust_trx_line_salesrep_id |
+-------------------------------------------------------------------*/
BEGIN
arp_ctls_pkg.set_to_dummy(l_srep_rec);
| Do the appropriate updates depending on which |
| parameters were passed in. |
+--------------------------------------------------*/
IF ( p_customer_trx_id IS NOT NULL )
THEN
BEGIN
arp_ctls_pkg.update_f_ct_id(l_srep_rec,
p_customer_trx_id);
arp_ctlgd_pkg.update_f_ct_id( l_dist_rec,
p_customer_trx_id,
null,
null);
arp_ctls_pkg.update_f_ctl_id(l_srep_rec,
p_customer_trx_line_id);
arp_ctlgd_pkg.update_f_ctl_id( l_dist_rec,
p_customer_trx_line_id,
null,
null);
arp_ctls_pkg.update_f_psr_id(l_srep_rec,
p_cust_trx_line_salesrep_id);
arp_ctlgd_pkg.update_f_ctls_id( l_dist_rec,
p_cust_trx_line_salesrep_id,
null,
null);
'EXCEPTION: arp_process_salescredit.delete_salescredit()');
| in the dynamic SQL update statement. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| dbms_sql.bind_variable |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_update_cursor - ID of the update cursor |
| p_srep_rec - ra_cust_trx_line_salesreps record |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE bind_srep_variables(p_update_cursor IN integer,
p_srep_rec IN ra_cust_trx_line_salesreps%rowtype)
IS
BEGIN
arp_util.debug('arp_ctls_pkg.bind_srep_variables()+');
dbms_sql.bind_variable(p_update_cursor, ':ar_text_dummy',
AR_TEXT_DUMMY);
dbms_sql.bind_variable(p_update_cursor, ':ar_number_dummy',
AR_NUMBER_DUMMY);
dbms_sql.bind_variable(p_update_cursor, ':ar_date_dummy',
AR_DATE_DUMMY);
dbms_sql.bind_variable(p_update_cursor, ':pg_user_id',
pg_user_id);
dbms_sql.bind_variable(p_update_cursor, ':pg_login_id',
pg_login_id);
dbms_sql.bind_variable(p_update_cursor, ':pg_conc_login_id',
pg_conc_login_id);
dbms_sql.bind_variable(p_update_cursor, ':cust_trx_line_salesrep_id',
p_srep_rec.cust_trx_line_salesrep_id);
dbms_sql.bind_variable(p_update_cursor, ':customer_trx_id',
p_srep_rec.customer_trx_id);
dbms_sql.bind_variable(p_update_cursor, ':customer_trx_line_id',
p_srep_rec.customer_trx_line_id);
dbms_sql.bind_variable(p_update_cursor, ':salesrep_id',
p_srep_rec.salesrep_id);
dbms_sql.bind_variable(p_update_cursor, ':revenue_amount_split',
p_srep_rec.revenue_amount_split);
dbms_sql.bind_variable(p_update_cursor, ':non_revenue_amount_split',
p_srep_rec.non_revenue_amount_split);
dbms_sql.bind_variable(p_update_cursor, ':non_revenue_percent_split',
p_srep_rec.non_revenue_percent_split);
dbms_sql.bind_variable(p_update_cursor, ':revenue_percent_split',
p_srep_rec.revenue_percent_split);
dbms_sql.bind_variable(p_update_cursor, ':prev_cust_trx_line_salesrep_id',
p_srep_rec.prev_cust_trx_line_salesrep_id);
dbms_sql.bind_variable(p_update_cursor, ':attribute_category',
p_srep_rec.attribute_category);
dbms_sql.bind_variable(p_update_cursor, ':attribute1',
p_srep_rec.attribute1);
dbms_sql.bind_variable(p_update_cursor, ':attribute2',
p_srep_rec.attribute2);
dbms_sql.bind_variable(p_update_cursor, ':attribute3',
p_srep_rec.attribute3);
dbms_sql.bind_variable(p_update_cursor, ':attribute4',
p_srep_rec.attribute4);
dbms_sql.bind_variable(p_update_cursor, ':attribute5',
p_srep_rec.attribute5);
dbms_sql.bind_variable(p_update_cursor, ':attribute6',
p_srep_rec.attribute6);
dbms_sql.bind_variable(p_update_cursor, ':attribute7',
p_srep_rec.attribute7);
dbms_sql.bind_variable(p_update_cursor, ':attribute8',
p_srep_rec.attribute8);
dbms_sql.bind_variable(p_update_cursor, ':attribute9',
p_srep_rec.attribute9);
dbms_sql.bind_variable(p_update_cursor, ':attribute10',
p_srep_rec.attribute10);
dbms_sql.bind_variable(p_update_cursor, ':attribute11',
p_srep_rec.attribute11);
dbms_sql.bind_variable(p_update_cursor, ':attribute12',
p_srep_rec.attribute12);
dbms_sql.bind_variable(p_update_cursor, ':attribute13',
p_srep_rec.attribute13);
dbms_sql.bind_variable(p_update_cursor, ':attribute14',
p_srep_rec.attribute14);
dbms_sql.bind_variable(p_update_cursor, ':attribute15',
p_srep_rec.attribute15);
dbms_sql.bind_variable(p_update_cursor, ':revenue_salesgroup_id',
p_srep_rec.revenue_salesgroup_id);
dbms_sql.bind_variable(p_update_cursor, ':non_revenue_salesgroup_id',
p_srep_rec.non_revenue_salesgroup_id);
dbms_sql.bind_variable(p_update_cursor, ':last_update_date',
p_srep_rec.last_update_date);
dbms_sql.bind_variable(p_update_cursor, ':last_updated_by',
p_srep_rec.last_updated_by);
dbms_sql.bind_variable(p_update_cursor, ':creation_date',
p_srep_rec.creation_date);
dbms_sql.bind_variable(p_update_cursor, ':created_by',
p_srep_rec.created_by);
dbms_sql.bind_variable(p_update_cursor, ':last_update_login',
p_srep_rec.last_update_login);
dbms_sql.bind_variable(p_update_cursor, ':program_application_id',
p_srep_rec.program_application_id);
dbms_sql.bind_variable(p_update_cursor, ':program_id',
p_srep_rec.program_id);
dbms_sql.bind_variable(p_update_cursor, ':program_update_date',
p_srep_rec.program_update_date);
| construct_srep_update_stmt |
| |
| DESCRIPTION |
| Copies the text of the dynamic SQL update statement into the |
| out NOCOPY paramater. The update statement does not contain a where clause |
| since this is the dynamic part that is added later. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| None. |
| OUT: |
| update_text - text of the update statement |
| |
| RETURNS : NONE |
| |
| NOTES |
| This statement only updates columns in the srep record that do not |
| contain the dummy values that indicate that they should not be changed.|
| |
| MODIFICATION HISTORY |
| 06-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE construct_srep_update_stmt( update_text OUT NOCOPY varchar2) IS
BEGIN
arp_util.debug('arp_ctls_pkg.construct_srep_update_stmt()+');
update_text :=
'UPDATE ra_cust_trx_line_salesreps
SET cust_trx_line_salesrep_id =
DECODE(:cust_trx_line_salesrep_id,
:ar_number_dummy, cust_trx_line_salesrep_id,
:cust_trx_line_salesrep_id),
customer_trx_id =
DECODE(:customer_trx_id,
:ar_number_dummy, customer_trx_id,
:customer_trx_id),
customer_trx_line_id =
DECODE(:customer_trx_line_id,
:ar_number_dummy, customer_trx_line_id,
:customer_trx_line_id),
salesrep_id =
DECODE(:salesrep_id,
:ar_number_dummy, salesrep_id,
:salesrep_id),
revenue_amount_split =
DECODE(:revenue_amount_split,
:ar_number_dummy, revenue_amount_split,
:revenue_amount_split),
non_revenue_amount_split =
DECODE(:non_revenue_amount_split,
:ar_number_dummy, non_revenue_amount_split,
:non_revenue_amount_split),
non_revenue_percent_split =
DECODE(:non_revenue_percent_split,
:ar_number_dummy, non_revenue_percent_split,
:non_revenue_percent_split),
revenue_percent_split =
DECODE(:revenue_percent_split,
:ar_number_dummy, revenue_percent_split,
:revenue_percent_split),
prev_cust_trx_line_salesrep_id =
DECODE(:prev_cust_trx_line_salesrep_id,
:ar_number_dummy, prev_cust_trx_line_salesrep_id,
:prev_cust_trx_line_salesrep_id),
attribute_category =
DECODE(:attribute_category,
:ar_text_dummy, attribute_category,
:attribute_category),
attribute1 =
DECODE(:attribute1,
:ar_text_dummy, attribute1,
:attribute1),
attribute2 =
DECODE(:attribute2,
:ar_text_dummy, attribute2,
:attribute2),
attribute3 =
DECODE(:attribute3,
:ar_text_dummy, attribute3,
:attribute3),
attribute4 =
DECODE(:attribute4,
:ar_text_dummy, attribute4,
:attribute4),
attribute5 =
DECODE(:attribute5,
:ar_text_dummy, attribute5,
:attribute5),
attribute6 =
DECODE(:attribute6,
:ar_text_dummy, attribute6,
:attribute6),
attribute7 =
DECODE(:attribute7,
:ar_text_dummy, attribute7,
:attribute7),
attribute8 =
DECODE(:attribute8,
:ar_text_dummy, attribute8,
:attribute8),
attribute9 =
DECODE(:attribute9,
:ar_text_dummy, attribute9,
:attribute9),
attribute10 =
DECODE(:attribute10,
:ar_text_dummy, attribute10,
:attribute10),
attribute11 =
DECODE(:attribute11,
:ar_text_dummy, attribute11,
:attribute11),
attribute12 =
DECODE(:attribute12,
:ar_text_dummy, attribute12,
:attribute12),
attribute13 =
DECODE(:attribute13,
:ar_text_dummy, attribute13,
:attribute13),
attribute14 =
DECODE(:attribute14,
:ar_text_dummy, attribute14,
:attribute14),
attribute15 =
DECODE(:attribute15,
:ar_text_dummy, attribute15,
:attribute15),
/* BEGIN bug 3067675 */
revenue_salesgroup_id =
DECODE(:revenue_salesgroup_id,
:ar_number_dummy, revenue_salesgroup_id,
:revenue_salesgroup_id),
non_revenue_salesgroup_id =
DECODE(:non_revenue_salesgroup_id,
:ar_number_dummy, non_revenue_salesgroup_id,
:non_revenue_salesgroup_id),
/* END bug 3067675 */
last_update_date =
DECODE(:last_update_date,
:ar_date_dummy, sysdate,
:last_update_date),
last_updated_by =
DECODE(:last_updated_by,
:ar_number_dummy, :pg_user_id,
:last_updated_by),
creation_date =
DECODE(:creation_date,
:ar_date_dummy, creation_date,
:creation_date),
created_by =
DECODE(:created_by,
:ar_number_dummy, created_by,
:created_by),
last_update_login =
DECODE(:last_update_login,
:ar_number_dummy, nvl(:pg_conc_login_id,
:pg_login_id),
:last_update_login),
program_application_id =
DECODE(:program_application_id,
:ar_number_dummy, program_application_id,
:program_application_id),
program_id =
DECODE(:program_id,
:ar_number_dummy, program_id,
:program_id),
program_update_date =
DECODE(:program_update_date,
:ar_date_dummy, program_update_date,
:program_update_date)';
arp_util.debug('arp_ctls_pkg.construct_srep_update_stmt()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.construct_srep_update_stmt()');
| generic_update |
| |
| DESCRIPTION |
| This procedure Updates records in ra_cust_trx_line_salesreps |
| identified by the where clause that is passed in as a parameter. Only |
| those columns in the srep record parameter that do not contain the |
| special dummy values are updated. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| dbms_sql.open_cursor |
| dbms_sql.parse |
| dbms_sql.execute |
| dbms_sql.close_cursor |
| |
| ARGUMENTS : IN: |
| p_update_cursor - identifies the cursor to use |
| p_where_clause - identifies which rows to update |
| p_where1 - value to bind into where clause |
| p_srep_rec - contains the new srep values |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
p_where_clause IN varchar2,
p_where1 IN number,
p_srep_rec IN ra_cust_trx_line_salesreps%rowtype)
IS
l_count number;
l_update_statement varchar2(25000);
arp_util.debug('arp_ctls_pkg.generic_update()+');
| If this update statement has not already been parsed, |
| construct the statement and parse it. |
| Otherwise, use the already parsed statement and rebind its |
| variables. |
+--------------------------------------------------------------*/
IF (p_update_cursor IS NULL)
THEN
p_update_cursor := dbms_sql.open_cursor;
| Construct the update statement |
+---------------------------------*/
arp_ctls_pkg.construct_srep_update_stmt(l_update_statement);
l_update_statement := l_update_statement || p_where_clause;
dbms_sql.parse(p_update_cursor,
l_update_statement,
dbms_sql.v7);
arp_ctls_pkg.bind_srep_variables(p_update_cursor, p_srep_rec);
dbms_sql.bind_variable(p_update_cursor, ':where_1',
p_where1);
l_count := dbms_sql.execute(p_update_cursor);
arp_util.debug( to_char(l_count) || ' rows updated');
| Raise the NO_DATA_FOUND exception if no rows were updated |
+------------------------------------------------------------*/
IF (l_count = 0)
THEN RAISE NO_DATA_FOUND;
dbms_sql.close_cursor(p_update_cursor);
arp_util.debug('arp_ctls_pkg.generic_update()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.generic_update()');
arp_util.debug(l_update_statement);
IF dbms_sql.is_open( p_update_cursor ) THEN
dbms_sql.close_cursor( p_update_cursor );
| select_summary |
| |
| DESCRIPTION |
| Selects the total revenue percent and amount for a given transaction |
| or line. This procedure is used by the forms running totals mechanism. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| p_customer_trx_line_id |
| p_amount_total |
| p_amount_total_rtot_db |
| p_percent_total |
| p_percent_total_rtot_db |
| OUT: |
| None |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 25-SEP-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE select_summary(p_customer_trx_id IN number,
p_customer_trx_line_id IN number,
p_mode IN varchar2,
p_amount_total IN OUT NOCOPY number,
p_amount_total_rtot_db IN OUT NOCOPY number,
p_percent_total IN OUT NOCOPY number,
p_percent_total_rtot_db IN OUT NOCOPY number ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('arp_ctls_pkg.select_summary()+');
SELECT NVL( SUM( NVL(revenue_amount_split, 0 ) ), 0),
NVL( SUM( NVL(revenue_amount_split, 0 ) ), 0),
NVL( SUM( NVL(revenue_percent_split, 0 ) ), 0),
NVL( SUM( NVL(revenue_percent_split, 0 ) ), 0)
INTO p_amount_total,
p_amount_total_rtot_db,
p_percent_total,
p_percent_total_rtot_db
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_id = p_customer_trx_id
AND NVL( customer_trx_line_id, -10 ) =
DECODE(p_mode,
'LINE', p_customer_trx_line_id,
'ALL', customer_trx_line_id,
-10 );
arp_util.debug('arp_ctls_pkg.select_summary()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.select_summary()');
END select_summary;
| last_update_date. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_cust_trx_line_salesrep_id |
| OUT: |
| None |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 13-JUL-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE display_salescredit( p_cust_trx_line_salesrep_id IN
ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type)
IS
l_srep_rec ra_cust_trx_line_salesreps%rowtype;
| last_update_date. |
| |
| SCOPE - PRIVATE |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_srep_rec |
| OUT: |
| None |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 17-JUL-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE display_salescredit_rec( p_srep_rec IN
ra_cust_trx_line_salesreps%rowtype)
IS
BEGIN
arp_util.debug('arp_ctls_pkg.display_salescredit_rec()+');
arp_util.debug('last_updated_by: ' || p_srep_rec.last_updated_by);
arp_util.debug('last_update_login: ' || p_srep_rec.last_update_login);
| last_update_date. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_line_id |
| OUT: |
| None |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 03-AUG-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE display_salescredit_f_ctl_id( p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type)
IS
CURSOR srep_cursor IS
SELECT *
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customeR_trx_line_id
ORDER BY salesrep_id,
revenue_amount_split,
non_revenue_amount_split,
cust_trx_line_salesrep_id;
p_srep_rec.last_update_date := AR_DATE_DUMMY;
p_srep_rec.last_updated_by := AR_NUMBER_DUMMY;
p_srep_rec.last_update_login := AR_NUMBER_DUMMY;
p_srep_rec.program_update_date := AR_DATE_DUMMY;
SELECT cust_trx_line_salesrep_id
INTO l_cust_trx_line_salesrep_id
FROM ra_cust_trx_line_salesreps
WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
FOR UPDATE OF cust_trx_line_salesrep_id NOWAIT;
SELECT cust_trx_line_salesrep_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_id = p_customer_trx_id
FOR UPDATE OF cust_trx_line_salesrep_id NOWAIT;
SELECT cust_trx_line_salesrep_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
FOR UPDATE OF cust_trx_line_salesrep_id NOWAIT;
SELECT *
INTO p_srep_rec
FROM ra_cust_trx_line_salesreps
WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
FOR UPDATE OF cust_trx_line_salesrep_id NOWAIT;
| that row have changed from when they were first selected in the form. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_cust_trx_line_salesrep_id - identifies the row to lock |
| p_srep_rec - srep record for comparison |
| p_ignore_who_flag - directs system to ignore who cols |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| 29-JUN-95 Charlie Tomberg Modified to use select for update |
| |
+===========================================================================*/
PROCEDURE lock_compare_p( p_srep_rec IN ra_cust_trx_line_salesreps%rowtype,
p_cust_trx_line_salesrep_id IN
ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type,
p_ignore_who_flag BOOLEAN DEFAULT FALSE) IS
l_new_srep_rec ra_cust_trx_line_salesreps%rowtype;
SELECT *
INTO l_new_srep_rec
FROM ra_cust_trx_line_salesreps tls
WHERE tls.cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id
AND
(
NVL(tls.cust_trx_line_salesrep_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.cust_trx_line_salesrep_id,
AR_NUMBER_DUMMY, tls.cust_trx_line_salesrep_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.cust_trx_line_salesrep_id),
AR_NUMBER_DUMMY
)
AND
NVL(tls.customer_trx_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.customer_trx_id,
AR_NUMBER_DUMMY, tls.customer_trx_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.customer_trx_id),
AR_NUMBER_DUMMY
)
AND
NVL(tls.customer_trx_line_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.customer_trx_line_id,
AR_NUMBER_DUMMY, tls.customer_trx_line_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.customer_trx_line_id),
AR_NUMBER_DUMMY
)
AND
NVL(tls.salesrep_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.salesrep_id,
AR_NUMBER_DUMMY, tls.salesrep_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.salesrep_id),
AR_NUMBER_DUMMY
)
AND
NVL(tls.revenue_amount_split, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.revenue_amount_split,
AR_NUMBER_DUMMY, tls.revenue_amount_split,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.revenue_amount_split),
AR_NUMBER_DUMMY
)
AND
NVL(tls.non_revenue_amount_split, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.non_revenue_amount_split,
AR_NUMBER_DUMMY, tls.non_revenue_amount_split,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.non_revenue_amount_split),
AR_NUMBER_DUMMY
)
AND
NVL(tls.non_revenue_percent_split, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.non_revenue_percent_split,
AR_NUMBER_DUMMY, tls.non_revenue_percent_split,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.non_revenue_percent_split),
AR_NUMBER_DUMMY
)
AND
NVL(tls.revenue_percent_split, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.revenue_percent_split,
AR_NUMBER_DUMMY, tls.revenue_percent_split,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.revenue_percent_split),
AR_NUMBER_DUMMY
)
AND
NVL(tls.prev_cust_trx_line_salesrep_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.prev_cust_trx_line_salesrep_id,
AR_NUMBER_DUMMY, tls.prev_cust_trx_line_salesrep_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.prev_cust_trx_line_salesrep_id),
AR_NUMBER_DUMMY
)
AND
NVL(tls.attribute_category, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute_category,
AR_TEXT_DUMMY, tls.attribute_category,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute_category),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute1, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute1,
AR_TEXT_DUMMY, tls.attribute1,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute1),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute2, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute2,
AR_TEXT_DUMMY, tls.attribute2,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute2),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute3, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute3,
AR_TEXT_DUMMY, tls.attribute3,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute3),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute4, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute4,
AR_TEXT_DUMMY, tls.attribute4,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute4),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute5, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute5,
AR_TEXT_DUMMY, tls.attribute5,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute5),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute6, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute6,
AR_TEXT_DUMMY, tls.attribute6,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute6),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute7, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute7,
AR_TEXT_DUMMY, tls.attribute7,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute7),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute8, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute8,
AR_TEXT_DUMMY, tls.attribute8,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute8),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute9, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute9,
AR_TEXT_DUMMY, tls.attribute9,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute9),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute10, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute10,
AR_TEXT_DUMMY, tls.attribute10,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute10),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute11, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute11,
AR_TEXT_DUMMY, tls.attribute11,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute11),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute12, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute12,
AR_TEXT_DUMMY, tls.attribute12,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute12),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute13, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute13,
AR_TEXT_DUMMY, tls.attribute13,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute13),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute14, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute14,
AR_TEXT_DUMMY, tls.attribute14,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute14),
AR_TEXT_DUMMY
)
AND
NVL(tls.attribute15, AR_TEXT_DUMMY) =
NVL(
DECODE(p_srep_rec.attribute15,
AR_TEXT_DUMMY, tls.attribute15,
NULL, AR_TEXT_DUMMY,
p_srep_rec.attribute15),
AR_TEXT_DUMMY
)
AND
/* BEGIN bug 3067675 */
NVL(tls.revenue_salesgroup_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.revenue_salesgroup_id,
AR_NUMBER_DUMMY, tls.revenue_salesgroup_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.revenue_salesgroup_id),
AR_NUMBER_DUMMY
)
AND
NVL(tls.non_revenue_salesgroup_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_srep_rec.non_revenue_salesgroup_id,
AR_NUMBER_DUMMY, tls.non_revenue_salesgroup_id,
NULL, AR_NUMBER_DUMMY,
p_srep_rec.non_revenue_salesgroup_id),
AR_NUMBER_DUMMY
)
AND
/* END bug 3067675 */
NVL(tls.last_update_date, AR_DATE_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.last_update_date, AR_DATE_DUMMY),
DECODE(
p_srep_rec.last_update_date,
AR_DATE_DUMMY, tls.last_update_date,
p_srep_rec.last_update_date
)
),
AR_DATE_DUMMY
)
AND
NVL(tls.last_updated_by, AR_NUMBER_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.last_updated_by, AR_NUMBER_DUMMY),
DECODE(
p_srep_rec.last_updated_by,
AR_NUMBER_DUMMY, tls.last_updated_by,
p_srep_rec.last_updated_by
)
),
AR_NUMBER_DUMMY
)
AND
NVL(tls.creation_date, AR_DATE_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.creation_date, AR_DATE_DUMMY),
DECODE(
p_srep_rec.creation_date,
AR_DATE_DUMMY, tls.creation_date,
p_srep_rec.creation_date
)
),
AR_DATE_DUMMY
)
AND
NVL(tls.created_by, AR_NUMBER_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.created_by, AR_NUMBER_DUMMY),
DECODE(
p_srep_rec.created_by,
AR_NUMBER_DUMMY, tls.created_by,
p_srep_rec.created_by
)
),
AR_NUMBER_DUMMY
)
AND
NVL(tls.last_update_login, AR_NUMBER_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.last_update_login, AR_NUMBER_DUMMY),
DECODE(
p_srep_rec.last_update_login,
AR_NUMBER_DUMMY, tls.last_update_login,
p_srep_rec.last_update_login
)
),
AR_NUMBER_DUMMY
)
AND
NVL(tls.program_application_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.program_application_id, AR_NUMBER_DUMMY),
DECODE(
p_srep_rec.program_application_id,
AR_NUMBER_DUMMY, tls.program_application_id,
p_srep_rec.program_application_id
)
),
AR_NUMBER_DUMMY
)
AND
NVL(tls.program_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.program_id, AR_NUMBER_DUMMY),
DECODE(
p_srep_rec.program_id,
AR_NUMBER_DUMMY, tls.program_id,
p_srep_rec.program_id
)
),
AR_NUMBER_DUMMY
)
AND
NVL(tls.program_update_date, AR_DATE_DUMMY) =
NVL(
DECODE(l_ignore_who_flag,
'Y', NVL(tls.program_update_date, AR_DATE_DUMMY),
DECODE(
p_srep_rec.program_update_date,
AR_DATE_DUMMY, tls.program_update_date,
p_srep_rec.program_update_date
)
),
AR_DATE_DUMMY
)
)
FOR UPDATE OF cust_trx_line_salesrep_id NOWAIT;
SELECT *
INTO p_srep_rec
FROM ra_cust_trx_line_salesreps
WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id;
| delete_p |
| |
| DESCRIPTION |
| This procedure deletes the ra_cust_trx_line_salesreps row identified |
| by the p_cust_trx_line_salesrep_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_cust_trx_line_salesrep_id - identifies the rows to delete |
| p_customer_trx_line_id |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 07-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
procedure delete_p( p_cust_trx_line_salesrep_id
IN ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type,
p_customer_trx_line_id
IN ra_customer_trx_lines.customer_trx_line_id%type )
IS
rows NUMBER;
arp_util.debug('arp_ctls_pkg.delete_p()+');
p_event_name => 'oracle.apps.ar.transaction.SalesCredit.delete',
p_event_key => to_char(p_cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
DELETE FROM ra_cust_trx_line_salesreps
WHERE cust_trx_line_salesrep_id = p_cust_trx_line_salesrep_id;
arp_util.debug( rows || ' records deleted');
THEN arp_util.debug('EXCEPTION: arp_ctls_pkg.delete_p()');
arp_util.debug('arp_ctls_pkg.delete_p()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.delete_p()');
| delete_f_ct_id |
| |
| DESCRIPTION |
| This procedure deletes the ra_cust_trx_line_salesreps rows identified |
| by the p_customer_trx_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id - identifies the rows to delete |
| p_delete_default_recs_flag |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 07-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
procedure delete_f_ct_id( p_customer_trx_id
IN ra_customer_trx.customer_trx_id%type,
p_delete_default_recs_flag IN boolean DEFAULT TRUE)
IS
CURSOR srep_trx_cursor IS
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where customer_trx_id = p_customer_trx_id;
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where customer_trx_id = p_customer_trx_id
and customer_trx_line_id IS NOT NULL;
arp_util.debug('arp_ctls_pkg.delete_f_ct_id()+');
IF ( p_delete_default_recs_flag = TRUE )
THEN
FOR l_srep_rec IN srep_trx_cursor LOOP
wf_event.raise(
p_event_name => 'oracle.apps.ar.transaction.SalesCredit.delete',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
DELETE FROM ra_cust_trx_line_salesreps
WHERE customer_trx_id = p_customer_trx_id;
p_event_name => 'oracle.apps.ar.transaction.SalesCredit.delete',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
DELETE FROM ra_cust_trx_line_salesreps
WHERE customer_trx_id = p_customer_trx_id
AND customer_trx_line_id IS NOT NULL;
arp_util.debug('arp_ctls_pkg.delete_f_ct_id()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.delete_f_ct_id()');
| delete_f_ctl_id |
| |
| DESCRIPTION |
| This procedure deletes the ra_cust_trx_line_salesreps rows identified |
| by the p_customer_trx_line_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_line_id - identifies the rows to delete |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 07-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
procedure delete_f_ctl_id( p_customer_trx_line_id
IN ra_customer_trx_lines.customer_trx_line_id%type)
IS
CURSOR srep_cursor IS
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where customer_trx_line_id = p_customer_trx_line_id;
arp_util.debug('arp_ctls_pkg.delete_f_ctl_id()+');
p_event_name => 'transaction.SalesCredit.apps.ar.salescredits.delete',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
DELETE FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id;
arp_util.debug('arp_ctls_pkg.delete_f_ctl_id()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.delete_f_ctl_id()');
| update_p |
| |
| DESCRIPTION |
| This procedure updates the ra_cust_trx_line_salesreps row identified |
| by the p_cust_trx_line_salesrep_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_cust_trx_line_salesrep_id - identifies the row to update |
| p_srep_rec - contains the new column values |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| set_to_dummy must be called before the values in p_srep_rec are |
| changed and this function is called. |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE update_p( p_srep_rec IN ra_cust_trx_line_salesreps%rowtype,
p_cust_trx_line_salesrep_id IN
ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type)
IS
BEGIN
arp_util.debug('arp_ctls_pkg.update_p()+ ' ||
to_char(sysdate, 'HH:MI:SS'));
p_event_name => 'oracle.apps.ar.transaction.SalesCredit.update',
p_event_key => to_char(p_cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
arp_ctls_pkg.generic_update( pg_cursor1,
' WHERE cust_trx_line_salesrep_id = :where_1',
p_cust_trx_line_salesrep_id,
p_srep_rec);
arp_util.debug('arp_ctls_pkg.update_p()- ' ||
to_char(sysdate, 'HH:MI:SS'));
arp_util.debug('EXCEPTION: arp_ctls_pkg.update_p()');
| update_f_ct_id |
| |
| DESCRIPTION |
| This procedure updates the ra_cust_trx_line_salesreps rows identified |
| by the p_customer_trx_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id - identifies the rows to update |
| p_srep_rec - contains the new column values |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| set_to_dummy must be called before the values in p_srep_rec are |
| changed and this function is called. |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE update_f_ct_id( p_srep_rec IN ra_cust_trx_line_salesreps%rowtype,
p_customer_trx_id IN ra_customer_trx.customer_trx_id%type)
IS
CURSOR srep_trx_cursor IS
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where customer_trx_id = p_customer_trx_id;
arp_util.debug('arp_ctls_pkg.update_f_ct_id()+ ' ||
to_char(sysdate, 'HH:MI:SS'));
p_event_name => 'oracle.apps.ar.transaction.SalesCredit.update',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
arp_ctls_pkg.generic_update( pg_cursor2,
' WHERE customer_trx_id = :where_1',
p_customer_trx_id,
p_srep_rec);
arp_util.debug('arp_ctls_pkg.update_f_ct_id()- ' ||
to_char(sysdate, 'HH:MI:SS'));
arp_util.debug('EXCEPTION: arp_ctls_pkg.update_f_ct_id()');
| update_f_ctl_id |
| |
| DESCRIPTION |
| This procedure updates the ra_cust_trx_line_salesreps rows identified |
| by the p_customer_trx_line_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_line_id - identifies the rows to update |
| p_srep_rec - contains the new column values |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| set_to_dummy must be called before the values in p_srep_rec are |
| changed and this function is called. |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE update_f_ctl_id( p_srep_rec IN ra_cust_trx_line_salesreps%rowtype,
p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type)
IS
CURSOR srep_cursor IS
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where customer_trx_line_id = p_customer_trx_line_id;
arp_util.debug('arp_ctls_pkg.update_f_ctl_id()+ ' ||
to_char(sysdate, 'HH:MI:SS'));
p_event_name => 'transaction.SalesCredit.apps.ar.salescredits.update',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
arp_ctls_pkg.generic_update( pg_cursor3,
' WHERE customer_trx_line_id = :where_1',
p_customer_trx_line_id,
p_srep_rec);
arp_util.debug('arp_ctls_pkg.update_f_ctl_id()- ' ||
to_char(sysdate, 'HH:MI:SS'));
arp_util.debug('EXCEPTION: arp_ctls_pkg.update_f_ctl_id()');
| update_f_psr_id |
| |
| DESCRIPTION |
| This procedure updates the ra_cust_trx_line_salesreps rows identified |
| by the prev_cust_trx_line_salesrep_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_prev_cust_trx_line_salesrep_id - identifies the rows |
| to update |
| p_srep_rec - contains the new column values |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| set_to_dummy must be called before the values in p_srep_rec are |
| changed and this function is called. |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE update_f_psr_id( p_srep_rec IN ra_cust_trx_line_salesreps%rowtype,
p_prev_cust_trx_line_srep_id
ra_cust_trx_line_salesreps.prev_cust_trx_line_salesrep_id%type)
IS
CURSOR srep_cursor IS
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where prev_cust_trx_line_salesrep_id = p_prev_cust_trx_line_srep_id;
arp_util.debug('arp_ctls_pkg.update_f_psr_id()+ ' ||
to_char(sysdate, 'HH:MI:SS'));
p_event_name => 'transaction.SalesCredit.apps.ar.salescredits.update',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
arp_ctls_pkg.generic_update(
pg_cursor3,
' WHERE prev_cust_trx_line_salesrep_id = :where_1',
p_prev_cust_trx_line_srep_id,
p_srep_rec);
arp_util.debug('arp_ctls_pkg.update_f_psr_id()- ' ||
to_char(sysdate, 'HH:MI:SS'));
arp_util.debug('EXCEPTION: arp_ctls_pkg.update_f_psr_id()');
| update_amounts_f_ctl_id |
| |
| DESCRIPTION |
| This procedure updates the ra_cust_trx_line_salesreps rows identified |
| by the p_customer_trx_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id - identifies the rows to update |
| p_srep_rec - contains the new column values |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| set_to_dummy must be called before the values in p_srep_rec are |
| changed and this function is called. |
| |
| MODIFICATION HISTORY |
| 08-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE update_amounts_f_ctl_id(
p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type,
p_line_amount IN
ra_customer_trx_lines.extended_amount%type,
p_foreign_currency_code IN
fnd_currencies.currency_code%type)
IS
CURSOR srep_cursor IS
select cust_trx_line_salesrep_id from ra_cust_trx_line_salesreps
where customer_trx_line_id = p_customer_trx_line_id;
arp_util.debug('arp_ctls_pkg.update_amounts_f_ctl_id()+ ' ||
to_char(sysdate, 'HH:MI:SS'));
p_event_name => 'transaction.SalesCredit.apps.ar.salescredits.update',
p_event_key => to_char(l_srep_rec.cust_trx_line_salesrep_id) || '_' ||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
update ra_cust_trx_line_salesreps
set last_updated_by = pg_user_id,
last_update_date = sysdate,
last_update_login = pg_login_id,
revenue_amount_split = arpcurr.CurrRound(
p_line_amount *
( revenue_percent_split / 100 ),
p_foreign_currency_code
),
non_revenue_amount_split = arpcurr.CurrRound(
p_line_amount *
( non_revenue_percent_split / 100 ),
p_foreign_currency_code
)
where customer_trx_line_id = p_customer_trx_line_id;
arp_util.debug(SQL%ROWCOUNT || ' rows updated');
arp_util.debug('arp_ctls_pkg.update_amounts_f_ctl_id()- ' ||
to_char(sysdate, 'HH:MI:SS'));
arp_util.debug('EXCEPTION: arp_ctls_pkg.update_amounts_f_ctl_id()');
| insert_p |
| |
| DESCRIPTION |
| This procedure inserts a row into ra_cust_trx_line_salesreps that |
| contains the column values specified in the p_srep_rec parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_srep_rec - contains the new column values |
| OUT: |
| p_cust_trx_line_salesrep_id - unique ID of the new row |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 06-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE insert_p(
p_srep_rec IN ra_cust_trx_line_salesreps%rowtype,
p_cust_trx_line_salesrep_id
OUT NOCOPY ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type
) IS
l_cust_trx_line_salesrep_id
ra_cust_trx_line_salesreps.cust_trx_line_salesrep_id%type;
arp_util.debug('arp_ctls_pkg.insert_p()+');
SELECT RA_CUST_TRX_LINE_SALESREPS_S.NEXTVAL
INTO l_cust_trx_line_salesrep_id
FROM DUAL;
| Insert the record |
*-------------------*/
INSERT INTO ra_cust_trx_line_salesreps
(
cust_trx_line_salesrep_id,
customer_trx_id,
customer_trx_line_id,
salesrep_id,
revenue_amount_split,
non_revenue_amount_split,
non_revenue_percent_split,
revenue_percent_split,
prev_cust_trx_line_salesrep_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
/* BEGIN bug 3067675 */
revenue_salesgroup_id,
non_revenue_salesgroup_id,
/* END bug 3067675 */
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date
,org_id
)
VALUES
(
l_cust_trx_line_salesrep_id,
p_srep_rec.customer_trx_id,
p_srep_rec.customer_trx_line_id,
p_srep_rec.salesrep_id,
p_srep_rec.revenue_amount_split,
p_srep_rec.non_revenue_amount_split,
p_srep_rec.non_revenue_percent_split,
p_srep_rec.revenue_percent_split,
p_srep_rec.prev_cust_trx_line_salesrep_id,
p_srep_rec.attribute_category,
p_srep_rec.attribute1,
p_srep_rec.attribute2,
p_srep_rec.attribute3,
p_srep_rec.attribute4,
p_srep_rec.attribute5,
p_srep_rec.attribute6,
p_srep_rec.attribute7,
p_srep_rec.attribute8,
p_srep_rec.attribute9,
p_srep_rec.attribute10,
p_srep_rec.attribute11,
p_srep_rec.attribute12,
p_srep_rec.attribute13,
p_srep_rec.attribute14,
p_srep_rec.attribute15,
/* BEGIN bug 3067675 */
p_srep_rec.revenue_salesgroup_id,
p_srep_rec.non_revenue_salesgroup_id,
/* END bug 3067675 */
sysdate, /*last_update_date */
pg_user_id, /* last_updated_by */
sysdate, /* creation_date */
pg_user_id, /* created_by */
nvl(pg_conc_login_id,
pg_login_id), /* last_update_login */
pg_prog_appl_id, /* program_application_id */
pg_conc_program_id, /* program_id */
sysdate /* program_update_date */
,arp_standard.sysparm.org_id /* SSA changes anuj */
);
arp_util.debug('arp_ctls_pkg.insert_p()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.insert_p()');
| insert_f_ct_ctl_id |
| |
| DESCRIPTION |
| This procedure inserts rows into ra_cust_trx_line_salesreps that |
| correspond to the default salescredits for the transaction |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_line_id |
| p_customer_trx_id |
| p_currency_code |
| p_precision |
| p_mau |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 24-JUN-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE insert_f_ct_ctl_id(
p_customer_trx_id IN
ra_customer_trx_lines.customer_trx_id%type,
p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type
) IS
BEGIN
arp_util.debug('arp_ctls_pkg.insert_f_ct_ctl_id()+');
SELECT salesrep_required_flag
INTO pg_salesrep_required_flag
FROM ar_system_parameters;
INSERT INTO ra_cust_trx_line_salesreps
(
cust_trx_line_salesrep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
customer_trx_line_id,
salesrep_id,
revenue_percent_split,
revenue_amount_split,
revenue_salesgroup_id,
non_revenue_percent_split,
non_revenue_amount_split,
non_revenue_salesgroup_id,
org_id
)
SELECT ra_cust_trx_line_salesreps_s.nextval,
sysdate, /* last_update_date */
pg_user_id, /* last_updated_by */
sysdate, /* creation_date */
pg_user_id, /* created_by */
nvl(pg_conc_login_id,
pg_login_id), /* last_update_login */
ctl.customer_trx_id,
ctl.customer_trx_line_id,
NVL( ctls.salesrep_id, ct.primary_salesrep_id ),
NVL(
ctls.revenue_percent_split,
DECODE(
ctls.cust_trx_line_salesrep_id,
null, 100,
null
)
),
arpcurr.CurrRound(
(
NVL(
ctls.revenue_percent_split,
DECODE(
ctls.cust_trx_line_salesrep_id,
null, 100,
null
)
)
/ 100
) * ctl.extended_amount,
ct.invoice_currency_code
),
NVL(
ctls.revenue_salesgroup_id,
DECODE(
ctls.cust_trx_line_salesrep_id,
null, arp_util.Get_Default_SalesGroup(ct.primary_salesrep_id, ct.org_id, ct.trx_date),
null
)
),
ctls.non_revenue_percent_split,
arpcurr.CurrRound(
(ctls.non_revenue_percent_split / 100 ) *
ctl.extended_amount,
ct.invoice_currency_code
),
ctls.non_revenue_salesgroup_id,
ct.org_id
FROM ra_customer_trx ct,
ra_cust_trx_line_salesreps ctls,
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = ctls.customer_trx_id(+)
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.customer_trx_id = p_customer_trx_id
AND ctl.line_type = 'LINE'
AND ctl.customer_trx_line_id = p_customer_trx_line_id
AND ctls.customer_trx_line_id(+) IS NULL
/*---------------------------------------------------------+
| Use the default salescredits if available. Otherwise, |
| insert a salescredit that corresponds to the primary |
| salesrep unless |
| 1) there is no primary salesrep or |
| 2) the primary salesrep is 'No Sales Credit' and |
| salescredits are not required. |
| 5250222 - default if primary is not null, even if
| it is -3 salesrep ID
+---------------------------------------------------------*/
AND (
( ctls.cust_trx_line_salesrep_id IS NOT NULL )
OR
( ct.primary_salesrep_id IS NOT NULL)
);
INSERT INTO ra_cust_trx_line_salesreps
(
cust_trx_line_salesrep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
customer_trx_line_id,
salesrep_id,
revenue_percent_split,
revenue_amount_split,
revenue_salesgroup_id, -- added here and in SELECT - bug 3067675
non_revenue_percent_split,
non_revenue_amount_split
,non_revenue_salesgroup_id -- added here and in SELECT - bug 3067675
,org_id
)
SELECT ra_cust_trx_line_salesreps_s.nextval,
sysdate, /* last_update_date */
pg_user_id, /* last_updated_by */
sysdate, /* creation_date */
pg_user_id, /* created_by */
nvl(pg_conc_login_id,
pg_login_id), /* last_update_login */
ctl.customer_trx_id,
ctl.customer_trx_line_id,
NVL( ctls.salesrep_id, ct.primary_salesrep_id ),
NVL(
ctls.revenue_percent_split,
DECODE(
ctls.cust_trx_line_salesrep_id,
null, 100,
null
)
),
arpcurr.CurrRound(
(
NVL(
ctls.revenue_percent_split,
DECODE(
ctls.cust_trx_line_salesrep_id,
null, 100,
null
)
)
/ 100
) * ctl.extended_amount,
ct.invoice_currency_code
),
NVL(
ctls.revenue_salesgroup_id,
DECODE(
ctls.cust_trx_line_salesrep_id,
null, arp_util.Get_Default_SalesGroup(ct.primary_salesrep_id, ct.org_id, ct.trx_date),
null
)
),
ctls.non_revenue_percent_split,
arpcurr.CurrRound(
(ctls.non_revenue_percent_split / 100 ) *
ctl.extended_amount,
ct.invoice_currency_code
)
,ctls.non_revenue_salesgroup_id
,ct.org_id
FROM ra_customer_trx ct,
ra_cust_trx_line_salesreps ctls,
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = ctls.customer_trx_id(+)
AND ctl.customer_trx_id = ct.customer_trx_id
AND ct.customer_trx_id = p_customer_trx_id
AND ctl.line_type = 'LINE'
AND ctls.customer_trx_line_id(+) IS NULL
/*---------------------------------------------------------+
| Use the default salescredits if available. Otherwise, |
| insert a salescredit that corresponds to the primary |
| salesrep unless |
| 1) there is no primary salesrep or |
| 2) the primary salesrep is 'No Sales Credit' and |
| salescredits are not required. |
+---------------------------------------------------------*/
AND (
( ctls.cust_trx_line_salesrep_id IS NOT NULL )
OR
( ct.primary_salesrep_id IS NOT NULL )
);
arp_util.debug('arp_ctls_pkg.insert_f_ct_ctl_id()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.insert_f_ct_ctl_id()');
| insert_f_cm_ct_ctl_id |
| |
| DESCRIPTION |
| This procedure inserts rows into ra_cust_trx_line_salesreps that |
| correspond to salescredits for the credited transaction or |
| a credited transaction line |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| p_customer_trx_line_id |
| p_currency_code |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 28-AUG-95 Subash Chadalavada Created |
| 05-SEP-02 J Beckett Bug 2543675 - RAM sales credits |
| excluded from credit memos |
| 19-NOV-02 J Beckett Bug 2543675 - change backed out. |
| |
+===========================================================================*/
PROCEDURE insert_f_cm_ct_ctl_id(
p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type,
p_currency_code IN fnd_currencies.currency_code%type
) IS
BEGIN
arp_util.debug('arp_ctls_pkg.insert_f_cm_ct_ctl_id()+');
INSERT INTO ra_cust_trx_line_salesreps
(
cust_trx_line_salesrep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
customer_trx_line_id,
salesrep_id,
revenue_salesgroup_id, -- added here and in SELECT - bug 3067675
non_revenue_salesgroup_id, -- added here and in SELECT - bug 3067675
prev_cust_trx_line_salesrep_id,
revenue_percent_split,
revenue_amount_split,
non_revenue_percent_split,
non_revenue_amount_split
,org_id
)
SELECT ra_cust_trx_line_salesreps_s.nextval,
sysdate, /* last_update_date */
pg_user_id, /* last_updated_by */
sysdate, /* creation_date */
pg_user_id, /* created_by */
nvl(pg_conc_login_id,
pg_login_id), /* last_update_login */
ctl.customer_trx_id,
ctl.customer_trx_line_id,
nvl(prev_ctls.salesrep_id, cm_ct.primary_salesrep_id),
decode(prev_ctls.cust_trx_line_salesrep_id,
null, arp_util.Get_Default_SalesGroup(cm_ct.primary_salesrep_id, cm_ct.org_id, cm_ct.trx_date),
prev_ctls.revenue_salesgroup_id),
prev_ctls.non_revenue_salesgroup_id,
prev_ctls.cust_trx_line_salesrep_id,
decode(prev_ctls.cust_trx_line_salesrep_id,
null, 100,
prev_ctls.revenue_percent_split),
arpcurr.CurrRound(
(decode(prev_ctls.cust_trx_line_salesrep_id,
null, 100,
prev_ctls.revenue_percent_split) / 100 ) *
ctl.extended_amount,
p_currency_code
), /* revenue_amount_split */
prev_ctls.non_revenue_percent_split,
arpcurr.CurrRound(
(prev_ctls.non_revenue_percent_split / 100 ) *
ctl.extended_amount,
p_currency_code
) /* non_revenue_amount_split */
,cm_ct.org_id
FROM ra_cust_trx_line_salesreps prev_ctls,
ra_customer_trx_lines ctl,
ra_customer_trx cm_ct
WHERE ctl.previous_customer_trx_id = prev_ctls.customer_trx_id(+)
AND ctl.previous_customer_trx_line_id =
prev_ctls.customer_trx_line_id(+)
AND ctl.customer_trx_id = p_customer_trx_id
AND cm_ct.customer_trx_id = p_customer_trx_id
AND ctl.line_type = 'LINE'
AND ctl.customer_trx_line_id = nvl(p_customer_trx_line_id,
ctl.customer_trx_line_id);
arp_util.debug('arp_ctls_pkg.insert_f_cm_ct_ctl_id() : '||
SQL%ROWCOUNT||' rows inserted');
arp_util.debug('arp_ctls_pkg.insert_f_cm_ct_ctl_id()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.insert_f_cm_ct_ctl_id()');
| insert_f_cmn_ct_ctl_id |
| |
| DESCRIPTION |
| This procedure inserts rows into ra_cust_trx_line_salesreps that |
| correspond to salescredits for the credit memo for a credited
| transaction line |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| p_customer_trx_line_id |
| OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 18-Jan-00 Satheesh Nambiar Created |
| 05-SEP-02 J Beckett Bug 2543675 - RAM sales credits |
| excluded from credit memos |
| 19-NOV-02 J Beckett Bug 2543675 - change backed out. |
| |
+===========================================================================*/
PROCEDURE insert_f_cmn_ct_ctl_id(
p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
p_customer_trx_line_id IN
ra_customer_trx_lines.customer_trx_line_id%type
) IS
BEGIN
arp_util.debug('arp_ctls_pkg.insert_f_cmn_ct_ctl_id()+');
INSERT INTO ra_cust_trx_line_salesreps
(
cust_trx_line_salesrep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
customer_trx_id,
customer_trx_line_id,
salesrep_id,
revenue_salesgroup_id, -- added here and in SELECT - bug 3067675
non_revenue_salesgroup_id, -- added here and in SELECT - bug 3067675
prev_cust_trx_line_salesrep_id,
revenue_percent_split,
revenue_amount_split,
non_revenue_percent_split,
non_revenue_amount_split
,org_id
)
SELECT ra_cust_trx_line_salesreps_s.nextval,
sysdate, /* last_update_date */
pg_user_id, /* last_updated_by */
sysdate, /* creation_date */
pg_user_id, /* created_by */
nvl(pg_conc_login_id,
pg_login_id), /* last_update_login */
ctl.customer_trx_id,
ctl.customer_trx_line_id,
nvl(prev_ctls.salesrep_id, cm_ct.primary_salesrep_id),
NVL(
prev_ctls.revenue_salesgroup_id,
DECODE(
prev_ctls.cust_trx_line_salesrep_id,
null, arp_util.Get_Default_SalesGroup(cm_ct.primary_salesrep_id, cm_ct.org_id, cm_ct.trx_date),
null
)
),
prev_ctls.non_revenue_salesgroup_id,
prev_ctls.cust_trx_line_salesrep_id,
NVL(
prev_ctls.revenue_percent_split,
DECODE(
prev_ctls.cust_trx_line_salesrep_id,
null, 100,
null
)
),
arpcurr.CurrRound(
(
NVL(
prev_ctls.revenue_percent_split,
DECODE(
prev_ctls.cust_trx_line_salesrep_id,
null, 100,
null
)
)
/ 100
) * ctl.extended_amount,
cm_ct.invoice_currency_code
),
prev_ctls.non_revenue_percent_split,
arpcurr.CurrRound(
(prev_ctls.non_revenue_percent_split / 100 ) *
ctl.extended_amount,
cm_ct.invoice_currency_code
) /* non_revenue_amount_split */
,cm_ct.org_id
FROM ra_cust_trx_line_salesreps prev_ctls,
ra_customer_trx_lines ctl,
ra_customer_trx cm_ct
WHERE ctl.previous_customer_trx_id = prev_ctls.customer_trx_id(+)
AND ctl.previous_customer_trx_line_id =
prev_ctls.customer_trx_line_id(+)
AND ctl.customer_trx_id = p_customer_trx_id
AND cm_ct.customer_trx_id = p_customer_trx_id
AND ctl.line_type = 'LINE'
AND ctl.customer_trx_line_id = nvl(p_customer_trx_line_id,
ctl.customer_trx_line_id);
arp_util.debug('arp_ctls_pkg.insert_f_cmn_ct_ctl_id() : '||
SQL%ROWCOUNT||' rows inserted');
arp_util.debug('arp_ctls_pkg.insert_f_cmn_ct_ctl_id()-');
arp_util.debug('EXCEPTION: arp_ctls_pkg.insert_f_cmn_ct_ctl_id()');