[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Validate_Adj_Insert |
| |
| DESCRIPTION |
| This is the routine that validates the inputs during creation|
| of adjustments |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| fnd_api.compatible_api_call |
| fnd_api.g_exc_unexpected_error |
| fnd_api.g_ret_sts_error |
| fnd_api.g_ret_sts_error |
| fnd_api.g_ret_sts_success |
| fnd_api.to_boolean |
| fnd_msg_pub.check_msg_level |
| fnd_msg_pub.count_and_get |
| fnd_msg_pub.initialize |
| ar_adjvalidate_pvt.Validate_Type |
| ar_adjvalidate_pvt.Validate_Payschd |
| ar_adjvalidate_pvt.Validate_amount |
| ar_adjvalidate_pvt.Validate_Rcvtrxccid |
| ar_adjvalidate_pvt.Validate_dates |
| ar_adjvalidate_pvt.Validate_Reason_code |
| ar_adjvalidate_pvt.Validate_doc_seq |
| ar_adjvalidate_pvt.Validate_Associated_Receipt |
| ar_adjvalidate_pvt.Validate_Ussgl_code |
| ar_adjvalidate_pvt.Validate_Desc_Flexfield |
| ar_adjvalidate_pvt.Validate_Created_From |
| |
| ARGUMENTS : IN: p_chk_approval_limits |
| p_check_amount |
| OUT: |
| IN/ OUT: p_Validation_status |
| p_adj_rec |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Vivek Halder 30-JUN-97 Created |
| Saloni Shah 03-FEB-00 Changes have been made for BR/BOE project. |
| Two new IN parameters have been added |
| - p_chk_approval_limits and p_check_amount|
| These parameters are passed to the |
| Validate_amount procedure. |
| Vat changes have also been made to calculate |
| the amounts if the adjustment type is 'LINE' |
| or 'CHARGES'. |
| |
| Satheesh Nambiar 25-Aug-00 Bug 1395396. Modified the code to process $0 |
| adjustment for LINE |
| V Crisostomo 09-OCT-02 Bug 2443950 : skip validate_doc_seq when |
| adjustment is against receivable_trx_id = -15|
+===========================================================================*/
PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
PROCEDURE Validate_Adj_Insert (
p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
p_chk_approval_limits IN varchar2,
p_check_amount IN varchar2,
p_validation_status IN OUT NOCOPY varchar2,
p_llca_from_call IN varchar2 DEFAULT 'N'
) IS
l_return_status varchar2(1);
arp_util.debug('Validate_Adj_Insert()+');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate type ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate payment_schedule id ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate dates ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate amount '|| p_validation_status);
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate receivables trx ccid');
| This need not be done if the insert of |
| adjustment is for reverse_adjustment, which |
| is indicated by the flag p_check_amount |
+--------------------------------------------------*/
--Bug 1395396 Calculate prorate only if adjustment amount <> 0
-- Added call to customer_trx_line_id for Line level Adjustment
IF (p_adj_rec.type in ('LINE', 'CHARGES') AND
p_check_amount = FND_API.G_TRUE AND
p_adj_rec.amount <> 0) THEN
ARP_PROCESS_ADJUSTMENT.cal_prorated_amounts(p_adj_rec.amount,
p_adj_rec.payment_schedule_id,
p_adj_rec.type,
p_adj_rec.receivables_trx_id,
p_adj_rec.apply_date,
l_prorated_amt,
l_prorated_tax,
l_error_num,
p_adj_rec.customer_trx_line_id);
arp_util.debug('Validate_Adj_Insert: ' || 'cal_prorated_amount failed - error num 1');
arp_util.debug('Validate_Adj_Insert: ' || 'cal_prorated_amount failed - error num 2');
arp_util.debug('Validate_Adj_Insert: ' || 'cal_prorated_amount failed - error num 3');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed over-application check ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed over_application_llca check ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate doc seq ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate reason code ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate associated receipt id ');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate ussgl_code');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate ussgl_code');
arp_util.debug ('Validate_Adj_Insert: ' || ' failed to validate created from ');
arp_util.debug('Validate_Adj_Insert()-');
arp_util.debug('Validate_Adj_Insert: ' || 'value of the status flag ' || p_validation_status);
arp_util.debug('EXCEPTION: Validate_Adj_Insert() ');
FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Adj_Insert');
END Validate_Adj_Insert;
| passed, then update the adjustment with that |
| id.
+===========================================================================*/
PROCEDURE Validate_Adj_modify (
p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
p_old_adj_rec IN ar_adjustments%rowtype,
p_chk_approval_limits IN varchar2,
p_validation_status IN OUT NOCOPY varchar2
) IS
l_ps_rec ar_payment_schedules%rowtype;
SELECT *
INTO l_ps_rec
FROM ar_payment_schedules
WHERE payment_schedule_id = p_old_adj_rec.payment_schedule_id;
SELECT cash_receipt_id
INTO l_cash_receipt_id
FROM ar_receivable_applications
WHERE status= 'APP'
AND display='Y'
AND receivable_application_id = p_adj_rec.associated_application_id
AND applied_payment_schedule_id= p_old_adj_rec.payment_schedule_id;
SELECT *
INTO l_ps_rec
FROM ar_payment_schedules
WHERE payment_schedule_id = p_old_adj_rec.payment_schedule_id;
SELECT *
INTO l_ps_rec
FROM ar_payment_schedules
WHERE payment_schedule_id = p_old_adj_rec.payment_schedule_id;
delete from ar_llca_adj_trx_lines_gt
where customer_trx_id = p_customer_trx_id;
delete from ar_llca_adj_trx_errors_gt
where customer_trx_id = p_customer_trx_id;
Insert into ar_llca_adj_trx_lines_gt
( customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
line_amount
)
values
(
p_customer_trx_id,
p_llca_adj_trx_lines_tbl(i).customer_trx_line_id,
p_llca_adj_trx_lines_tbl(i).receivables_trx_id,
p_llca_adj_trx_lines_tbl(i).line_amount
);
select count(*) into ll_installment
from ar_payment_schedules
where class in ('INV','DM')
and customer_trx_id = p_adj_rec.customer_trx_id;
| Validate_Adj_Insert procedure. |
| p_chk_approval_limits flag indicates whether |
| the adjustment amount should be validated |
| against the users approval limits or not. |
| p_check_amount is set to 'F' in case of |
| adjustment reversal only, this flag |
| when set to 'F' indicates that even if the |
| adjustment type is 'INVOICE'the |
| amount_due_remaining will not be zero. |
| SNAMBIAR 04-May-00 Bug 1290698 |
| Added Proration logic for partial payments |
| SNAMBIAR 05-Sep-00 Bug 1392055 - Call prorate routine only when
| PS.amount_due_remaining > 0
| SNAMBIAR 27-Sep-00 Added new parameters p_called_from and
| p_old_adj_id for reverse
| AMMISHRA 13-Feb-02 Set l_override_flag to Y if CCID is
| overridden through ADjustment API.Then the
| l_override_flag is passed to insert_adjustment
| procedure.
+===========================================================================*/
PROCEDURE Create_Adjustment (
p_api_name IN varchar2,
p_api_version IN number,
p_init_msg_list IN varchar2 := FND_API.G_FALSE,
p_commit_flag IN varchar2 := FND_API.G_FALSE,
p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_return_status OUT NOCOPY varchar2 ,
p_adj_rec IN ar_adjustments%rowtype,
p_chk_approval_limits IN varchar2 := FND_API.G_TRUE,
p_check_amount IN varchar2 := FND_API.G_TRUE,
p_move_deferred_tax IN varchar2,
p_new_adjust_number OUT NOCOPY ar_adjustments.adjustment_number%type,
p_new_adjust_id OUT NOCOPY ar_adjustments.adjustment_id%type,
p_called_from IN varchar2,
p_old_adjust_id IN ar_adjustments.adjustment_id%type,
p_org_id IN NUMBER DEFAULT NULL
) IS
l_api_name CONSTANT VARCHAR2(20) := 'AR_ADJUST_PUB';
select hsecs
into G_START_TIME
from v$timer;
ar_adjust_pub.Validate_Adj_Insert(
l_inp_adj_rec,
l_chk_approval_limits,
l_check_amount,
l_return_status
);
| Call the entity Handler for insert |
+-----------------------------------------------*/
/*Bug 2183969 set l_override_flag to Y and passed to
insert_adjustment function if code_combination_id is
overridden through Adjustment API.
*/
IF (l_inp_adj_rec.code_combination_id is NOT NULL) THEN
l_override_flag := 'Y';
arp_process_adjustment.insert_adjustment (
'DUMMY',
'1',
l_inp_adj_rec,
o_adjustment_number,
o_adjustment_id,
l_check_amount,
p_move_deferred_tax,
p_called_from,
p_old_adjust_id,
l_override_flag
) ;
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.insert_adjustment exception: '||SQLERRM );
'Error in Insert Entity handler. Rolling back ' ||
'and setting status to ERROR');
select TO_CHAR( (hsecs - G_START_TIME) / 100)
into l_hsec
from v$timer;
select * from ar_llca_adj_trx_lines_gt
where customer_trx_id = p_cust_trx_id;
select hsecs
into G_START_TIME
from v$timer;
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_inp_adj_rec.customer_trx_id,
0,
l_inp_adj_rec.receivables_trx_id,
'AR_RAPI_TRX_LINE_ID_INVALID ',
'customer_trx_line_id'
);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_inp_adj_rec.customer_trx_id,
l_inp_adj_rec.customer_trx_line_id,
0,
'AR_RAPI_RECEIVABLES_TRX_ID_INVALID ',
'receivables_trx_id'
);
ar_adjust_pub.Validate_Adj_Insert(
l_inp_adj_rec,
l_chk_approval_limits,
l_check_amount,
l_return_status,
'Y'
);
| Call the entity Handler for insert |
+-----------------------------------------------*/
IF ( l_line_return_status = FND_API.G_RET_STS_SUCCESS ) THEN -- Processing the adjustment
IF (l_inp_adj_rec.code_combination_id is NOT NULL) THEN
l_override_flag := 'Y';
arp_process_adjustment.insert_adjustment (
'DUMMY',
'1',
l_inp_adj_rec,
o_adjustment_number,
o_adjustment_id,
l_check_amount,
p_move_deferred_tax,
p_called_from,
p_old_adjust_id,
l_override_flag,
'LINE'
) ;
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.insert_adjustment exception: '||SQLERRM );
'Error in Insert Entity handler. Rolling back ' ||
'and setting status to ERROR');
select TO_CHAR( (hsecs - G_START_TIME) / 100)
into l_hsec
from v$timer;
| ar_process_adjustment.update_adjustment |
| ar_adjvalidate_pvt.within_approval_limit |
| |
| ARGUMENTS : IN: |
| p_api_name |
| p_api_version |
| p_init_msg_list |
| p_commit_flag |
| p_validation_level |
| p_adj_rec |
| p_chk_approval_limits |
| p_old_adjust_id |
| OUT: |
| p_return_status |
| p_msg_count |
| p_msg_data |
| |
| IN/ OUT: |
| |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Vivek Halder 30-JAN-97 Created |
| Saloni Shah 03-FEB-00 Changes have been made for the BR/BOE project|
| One new parameter has been added: |
| - p_chk_approval_limits. |
| This parameter will be passed to |
| Validate_Adj_Modify procedure. |
| If the value of the flag p_chk_approval_limit|
| is set to 'F' then the adjusted amount will |
| not be validated against the users approval |
| limits. |
| Satheesh Nambiar 17-May-00 Added one more parameter p_move_deferred_tax|
| for BOE/BR
+===========================================================================*/
PROCEDURE Modify_Adjustment (
p_api_name IN varchar2,
p_api_version IN number,
p_init_msg_list IN varchar2 := FND_API.G_FALSE,
p_commit_flag IN varchar2 := FND_API.G_FALSE,
p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_return_status OUT NOCOPY varchar2 ,
p_adj_rec IN ar_adjustments%rowtype,
p_chk_approval_limits IN varchar2 := FND_API.G_TRUE,
p_move_deferred_tax IN varchar2,
p_old_adjust_id IN ar_adjustments.adjustment_id%type,
p_org_id IN NUMBER DEFAULT NULL
) IS
l_api_name CONSTANT VARCHAR2(20) := 'AR_ADJUST_PUB';
select hsecs
into G_START_TIME
from v$timer;
arp_process_adjustment.update_adjustment (
'DUMMY',
'1',
l_inp_adj_rec,
p_move_deferred_tax,
p_old_adjust_id
) ;
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.update_adjustment exception: '||SQLERRM );
'Error in Insert Entity handler. Rolling back '||
'and setting status to ERROR');
select TO_CHAR( (hsecs - G_START_TIME) / 100)
into l_hsec
from v$timer;
| not update the payment schedules. This has |
| been changed to call Create Adjustment. |
| S.Nambiar 27-Sep-00 Added a new parameter p_called_from for BR |
| S.Nambiar 28-Nov-00 Bug 1449758 - Modified Reverse routine to |
| initialize gl_posted_date and posting_control_id
+===========================================================================*/
PROCEDURE Reverse_Adjustment (
p_api_name IN varchar2,
p_api_version IN number,
p_init_msg_list IN varchar2 := FND_API.G_FALSE,
p_commit_flag IN varchar2 := FND_API.G_FALSE,
p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_return_status OUT NOCOPY varchar2,
p_old_adjust_id IN ar_adjustments.adjustment_id%type,
p_reversal_gl_date IN date,
p_reversal_date IN date,
p_comments IN ar_adjustments.comments%type,
p_chk_approval_limits IN varchar2 := FND_API.G_TRUE,
p_move_deferred_tax IN varchar2,
p_new_adj_id OUT NOCOPY ar_adjustments.adjustment_id%type,
p_called_from IN varchar2,
p_org_id IN NUMBER DEFAULT NULL
) IS
l_api_name CONSTANT VARCHAR2(20) := 'AR_ADJUST_PUB';
select hsecs
into G_START_TIME
from v$timer;
Select count(*)
into l_count_chk
from AR_ACTIVITY_DETAILS
where source_id = p_old_adjust_id
and source_table = 'ADJ'
and nvl(CURRENT_ACTIVITY_FLAG,'Y') = 'Y' -- Bug 7241111
and customer_trx_line_id = l_adj_rec.customer_trx_line_id;
| Call the create adjustment api to insert the new adjustment. |
+--------------------------------------------------------------*/
BEGIN
IF NVL(l_count_chk,0) <> 0
THEN
ar_adjust_pub.create_linelevel_adjustment(
p_api_name => 'AR_ADJUST_PUB',
p_api_version => 1.0,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_adj_rec => l_adj_rec,
p_chk_approval_limits => l_chk_approval_limits,
p_llca_adj_trx_lines_tbl => l_llca_adj_trx_lines_tbl,
p_check_amount => l_check_amount,
p_move_deferred_tax => p_move_deferred_tax,
p_llca_adj_create_tbl_type => l_llca_adj_create_tbl_type,
p_called_from => p_called_from,
p_old_adjust_id => p_old_adjust_id);
'Error in Insert Entity handler. Rolling back ' ||
'and setting status to ERROR');
select TO_CHAR( (hsecs - G_START_TIME) / 100)
into l_hsec
from v$timer;
| ar_process_adjustment.update_approve_adjustment |
| |
| ARGUMENTS : IN: |
| p_api_name |
| p_api_version |
| p_init_msg_list |
| p_commit_flag |
| p_validation_level |
| p_adj_rec |
| p_chk_approval_limits |
| p_old_adjust_id |
| OUT: |
| p_return_status |
| p_msg_count |
| p_msg_data |
| |
| IN/ OUT: |
| |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| Vivek Halder 30-JAN-97 Created |
| |
| Saloni Shah 03-FEB-00 Changes have been made for the BR/BOE project|
| One new parameter has been added: |
| - p_chk_approval_limits. |
| This parameter will be passed to |
| Validate_Adj_Approve procedure. |
| If the value of the flag p_chk_approval_limit|
| is set to 'F' then the adjusted amount will |
| not be validated against the users approval |
| limits. |
| Satheesh Nambiar 17-May-00 Added one more parameter p_move_deferred_tax|
| for BOE/BR
| |
+===========================================================================*/
PROCEDURE Approve_Adjustment (
p_api_name IN varchar2,
p_api_version IN number,
p_init_msg_list IN varchar2 := FND_API.G_FALSE,
p_commit_flag IN varchar2 := FND_API.G_FALSE,
p_validation_level IN number := FND_API.G_VALID_LEVEL_FULL,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_return_status OUT NOCOPY varchar2 ,
p_adj_rec IN ar_adjustments%rowtype,
p_chk_approval_limits IN varchar2 := FND_API.G_TRUE,
p_move_deferred_tax IN varchar2,
p_old_adjust_id IN ar_adjustments.adjustment_id%type,
p_org_id IN NUMBER DEFAULT NULL
) IS
l_api_name CONSTANT VARCHAR2(20) := 'AR_ADJUST_PUB';
select hsecs
into G_START_TIME
from v$timer;
arp_process_adjustment.update_approve_adj (
'DUMMY',
'1',
l_inp_adj_rec,
l_inp_adj_rec.status,
p_old_adjust_id ,
p_chk_approval_limits,
p_move_deferred_tax
) ;
FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.update_approve_adj exception: '||SQLERRM );
'Error in Insert Entity handler. Rolling back ' ||
'and setting status to ERROR');
select TO_CHAR( (hsecs - G_START_TIME) / 100)
into l_hsec
from v$timer;