[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_ADJ(p_ae_deleted OUT NOCOPY BOOLEAN);
| PUBLIC PROCEDURE Delete_Acct
|
| DESCRIPTION
| Accounting Entry Deletion
| -------------------------
| This procedure is the Accounting Entry deletion routine which
| deletes data associated with Adjustments based on event and source
| table.
|
| PARAMETERS
| p_mode IN Document or Accounting Event mode
| p_ae_doc_rec IN Document Record
| p_ae_event_rec IN Event Record
| p_ae_deleted OUT NOCOPY AE Lines deletion status
* ======================================================================*/
PROCEDURE Delete_Acct( p_mode IN VARCHAR2,
p_ae_doc_rec IN ae_doc_rec_type,
p_ae_event_rec IN ae_event_rec_type,
p_ae_deleted OUT NOCOPY BOOLEAN ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Delete_Acct()+');
Delete_ADJ(p_ae_deleted => p_ae_deleted) ;
arp_standard.debug( 'ARP_ADJUSTMENTS_MAIN.Delete_Acct()-');
arp_standard.debug( 'EXCEPTION: ARP_ADJUSTMENTS_MAIN.Delete_Acct');
END Delete_Acct;
| PROCEDURE Delete_ADJ
|
| DESCRIPTION
| Deletes accounting associated with a Adjustment id from the
| AR_DISTRIBUTIONS table.This routine deletes all records
| matching the input source_id. Note records from child table
| (AR_DISTRIBUTIONS) be deleted first.
|
| PARAMETERS
| p_ae_deleted indicates whether records were deleted
| for source_id
* ======================================================================*/
PROCEDURE Delete_ADJ(p_ae_deleted OUT NOCOPY BOOLEAN) IS
l_source_id ar_distributions.source_id%TYPE;
arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Delete_ADJ()+');
SELECT adj.adjustment_id
INTO l_source_id
FROM ar_adjustments adj
WHERE adj.adjustment_id = g_ae_doc_rec.source_id
AND adj.posting_control_id = -3
AND g_ae_doc_rec.source_table = 'ADJ'
AND EXISTS (SELECT 'x'
FROM ar_distributions ard
WHERE ard.source_id = adj.adjustment_id
AND ard.source_table = 'ADJ');
| Delete all accounting for source id and source table combination |
| if valid candidate for deletion |
+-------------------------------------------------------------------*/
-- modified for mrc trigger elimination.
DELETE FROM AR_DISTRIBUTIONS
WHERE source_id = l_source_id
AND source_table = 'ADJ'
RETURNING line_id
BULK COLLECT INTO l_ar_dist_key_value_list;
arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Delete_ADJ()-');
arp_standard.debug('ARP_ADJUSTMENTS_MAIN.Delete_ADJ - NO_DATA_FOUND' );
p_ae_deleted := FALSE;
arp_standard.debug('EXCEPTION: ARP_ADJUSTMENTS_MAIN.Delete_ADJ');
p_ae_deleted := FALSE;
END Delete_ADJ;
select ard.source_type,
ard.source_id_secondary,
ard.source_type_secondary,
ard.source_table_secondary,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.taxable_entered_cr,
ard.taxable_entered_dr,
ard.taxable_accounted_cr,
ard.taxable_accounted_dr,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_group_code_id,
ard.tax_code_id,
ard.location_segment_id,
ard.tax_link_id,
--{BUG#2979254
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
--}
--{3377004
DECODE( ard.ref_customer_trx_line_id, NULL,'N',
DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET,
--}
ard.ref_account_class,
ard.activity_bucket,
ard.ref_dist_ccid
from ar_distributions ard,
ar_adjustments adj
where g_ae_sys_rec.sob_type = 'P'
and ard.source_id = g_ae_doc_rec.source_id_old
and ard.source_table = g_ae_doc_rec.source_table
and nvl(ard.source_type_secondary,'X') NOT IN
('ASSIGNMENT_RECONCILE','RECONCILE')
and adj.adjustment_id(+) = g_ae_doc_rec.source_id_old --3377004
UNION
select ard.source_type,
ard.source_id_secondary,
ard.source_type_secondary,
ard.source_table_secondary,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.taxable_entered_cr,
ard.taxable_entered_dr,
ard.taxable_accounted_cr,
ard.taxable_accounted_dr,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_group_code_id,
ard.tax_code_id,
ard.location_segment_id,
ard.tax_link_id,
--{BUG#2979254
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
--}
--{3377004
DECODE( ard.ref_customer_trx_line_id, NULL,'N',
DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET,
--}
ard.ref_account_class,
ard.activity_bucket,
ard.ref_dist_ccid
from ar_distributions ard,
ar_adjustments adj
where g_ae_sys_rec.sob_type = 'P'
and adj.adjustment_id = g_ae_doc_rec.source_id_old
and ard.source_id = adj.link_to_trx_hist_id
and ard.source_table = 'TH' --for Bills Receivable Standard/Factored
and nvl(ard.source_type_secondary,'X') NOT IN
('ASSIGNMENT_RECONCILE','RECONCILE')
and nvl(g_ae_doc_rec.event,'NONE') <> 'RISK_UNELIMINATED'
order by 1 ;
select ard.source_type,
ard.source_id_secondary,
ard.source_type_secondary,
ard.source_table_secondary,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.taxable_entered_cr,
ard.taxable_entered_dr,
ard.taxable_accounted_cr,
ard.taxable_accounted_dr,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_group_code_id,
ard.tax_code_id,
ard.location_segment_id,
ard.tax_link_id,
--{BUG#2979254
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
--}
--{3377004
DECODE( ard.ref_customer_trx_line_id, NULL,'N',
DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET
--}
from ar_mc_distributions_all ard,
ar_adjustments adj
where g_ae_sys_rec.sob_type = 'R'
and ard.set_of_books_id = g_ae_sys_rec.set_of_books_id
and ard.source_id = g_ae_doc_rec.source_id_old
and ard.source_table = g_ae_doc_rec.source_table
and nvl(ard.source_type_secondary,'X') NOT IN
('ASSIGNMENT_RECONCILE','RECONCILE')
and adj.adjustment_id(+) = g_ae_doc_rec.source_id_old --3377004
UNION
select ard.source_type,
ard.source_id_secondary,
ard.source_type_secondary,
ard.source_table_secondary,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.taxable_entered_cr,
ard.taxable_entered_dr,
ard.taxable_accounted_cr,
ard.taxable_accounted_dr,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_group_code_id,
ard.tax_code_id,
ard.location_segment_id,
ard.tax_link_id,
--{BUG#2979254
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
--}
--{3377004
DECODE( ard.ref_customer_trx_line_id, NULL,'N',
DECODE(adj.type,'CHARGES','ADDCHRG','ADDCTL')) WHICH_BUCKET
--}
from ar_mc_distributions_all ard,
ar_adjustments adj
where g_ae_sys_rec.sob_type = 'R'
and g_ae_sys_rec.set_of_books_id = ard.set_of_books_id
and adj.adjustment_id = g_ae_doc_rec.source_id_old
and ard.source_id = adj.link_to_trx_hist_id
and ard.source_table = 'TH' --for Bills Receivable Standard/Factored
and nvl(ard.source_type_secondary,'X') NOT IN
('ASSIGNMENT_RECONCILE','RECONCILE')
and nvl(g_ae_doc_rec.event,'NONE') <> 'RISK_UNELIMINATED'
order by 1 ;
SELECT adj.customer_trx_id customer_trx_id,
adj.amount amount,
--{BUG4301323
-- DECODE(g_ae_sys_rec.sob_type, 'P',
-- adj.acctd_amount,
-- arp_mrc_acct_main.get_adj_entity_data(
-- adj.adjustment_id,
-- g_ae_sys_rec.set_of_books_id)) acctd_amount,
adj.acctd_amount acctd_amount,
--}
ctinv.invoice_currency_code invoice_currency_code ,
--{BUG4301323
-- DECODE(g_ae_sys_rec.sob_type, 'P',
-- ctinv.exchange_rate,
-- arp_mrc_acct_main.get_ctx_exg_rate(
-- ctinv.customer_trx_id,
-- g_ae_sys_rec.set_of_books_id)) exchange_rate,
ctinv.exchange_rate exchange_rate,
--}
--{BUG4301323
-- DECODE(g_ae_sys_rec.sob_type, 'P',
-- ctinv.exchange_rate_type,
-- arp_mrc_acct_main.get_ctx_exg_rate_type(
-- ctinv.customer_trx_id,
-- g_ae_sys_rec.set_of_books_id)) exchange_rate_type,
ctinv.exchange_rate_type exchange_rate_type,
--}
--{BUG4301323
-- DECODE(g_ae_sys_rec.sob_type, 'P',
-- ctinv.exchange_date,
-- arp_mrc_acct_main.get_ctx_exg_date(
-- ctinv.customer_trx_id,
-- g_ae_sys_rec.set_of_books_id)) exchange_date,
ctinv.exchange_date exchange_date,
--}
ctinv.trx_date trx_date,
ctinv.bill_to_customer_id bill_to_customer_id,
ctinv.bill_to_site_use_id bill_to_site_use_id,
ctinv.drawee_id drawee_id,
ctinv.drawee_site_use_id drawee_site_use_id
from ar_adjustments adj,
ra_customer_trx ctinv
where adj.adjustment_id = g_ae_doc_rec.source_id_old
and adj.status = 'A'
and g_ae_doc_rec.source_table = 'ADJ'
and adj.customer_trx_id = ctinv.customer_trx_id;
UPDATE ra_customer_trx_lines
SET AMOUNT_DUE_REMAINING = AMOUNT_DUE_REMAINING + l_ctl_rem_amt.amount_due_remaining(m),
ACCTD_AMOUNT_DUE_REMAINING = ACCTD_AMOUNT_DUE_REMAINING + l_ctl_rem_amt.acctd_amount_due_remaining(m),
CHRG_AMOUNT_REMAINING = CHRG_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_amount_remaining(m),
CHRG_ACCTD_AMOUNT_REMAINING = CHRG_ACCTD_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_acctd_amount_remaining(m)
WHERE customer_trx_line_id = l_ctl_rem_amt.customer_trx_line_id(m);
select adj.adjustment_id ,
adj.customer_trx_id ,
adj.customer_trx_line_id ,
adj.payment_schedule_id ,
adj.receivables_trx_id ,
adj.code_combination_id ,
adj.apply_date ,
adj.gl_date ,
adj.type ,
adj.status ,
adj.amount ,
adj.acctd_amount ,
adj.line_adjusted ,
adj.freight_adjusted ,
adj.tax_adjusted ,
adj.receivables_charges_adjusted ,
ctinv.invoice_currency_code ,
ctinv.exchange_rate exchange_rate,
ctinv.exchange_rate_type exchange_rate_type,
ctinv.exchange_date exchange_date,
ctinv.bill_to_customer_id ,
ctinv.bill_to_site_use_id ,
ctinv.drawee_id ,
ctinv.drawee_site_use_id ,
ctinv.upgrade_method ,
ctlgdinv.code_combination_id ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL', 'ACTIVITY_GL_ACCOUNT', --trx id -12
'CHARGEBACK', 'ACTIVITY_GL_ACCOUNT', --trx id -11
'COMMITMENT', 'ACTIVITY_GL_ACCOUNT', --trx od -1
nvl(rt.gl_account_source, 'NO_SOURCE')) ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL', 'NONE', --trx id -12
'CHARGEBACK', 'NONE', --trx id -11
'COMMITMENT', 'NONE', --trx id -1
nvl(rt.tax_code_source , 'NO_SOURCE')) ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL', '',
'CHARGEBACK', '',
'COMMITMENT', '',
rt.tax_recoverable_flag) ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL',g_ae_doc_rec.source_id_old, --chargeback reversal
'CHARGEBACK',g_ae_doc_rec.source_id_old, --chargeback
'COMMITMENT',g_ae_doc_rec.source_id_old, --commitments
'OVERRIDE' ,g_ae_doc_rec.source_id_old, --when user specifies account
rt.code_combination_id) , --in adjustment form
nvl(rtd.asset_tax_code, rt.asset_tax_code) ,
nvl(rtd.liability_tax_code, rt.liability_tax_code),
'' ,
'' ,
'NO_SOURCE' ,
'NO_SOURCE' ,
'' ,
'' ,
'' ,
'' ,
'' ,
''
into p_adj_rec.adjustment_id ,
p_adj_rec.customer_trx_id ,
p_adj_rec.customer_trx_line_id ,
p_adj_rec.payment_schedule_id ,
p_adj_rec.receivables_trx_id ,
p_adj_rec.code_combination_id ,
p_adj_rec.apply_date ,
p_adj_rec.gl_date ,
p_adj_rec.type ,
p_adj_rec.status ,
p_adj_rec.amount ,
p_adj_rec.acctd_amount ,
p_adj_rec.line_adjusted ,
p_adj_rec.freight_adjusted ,
p_adj_rec.tax_adjusted ,
p_adj_rec.receivables_charges_adjusted ,
p_cust_inv_rec.invoice_currency_code ,
p_cust_inv_rec.exchange_rate ,
p_cust_inv_rec.exchange_rate_type ,
p_cust_inv_rec.exchange_date ,
p_cust_inv_rec.bill_to_customer_id ,
p_cust_inv_rec.bill_to_site_use_id ,
p_cust_inv_rec.drawee_id ,
p_cust_inv_rec.drawee_site_use_id ,
p_cust_inv_rec.upgrade_method ,
p_ctlgd_inv_rec.code_combination_id ,
p_rule_rec.gl_account_source1 ,
p_rule_rec.tax_code_source1 ,
p_rule_rec.tax_recoverable_flag1 ,
p_rule_rec.code_combination_id1 ,
p_rule_rec.asset_tax_code1 ,
p_rule_rec.liability_tax_code1 ,
p_rule_rec.act_tax_non_rec_ccid1 ,
p_rule_rec.act_vat_tax_id1 ,
p_rule_rec.gl_account_source2 ,
p_rule_rec.tax_code_source2 ,
p_rule_rec.tax_recoverable_flag2 ,
p_rule_rec.code_combination_id2 ,
p_rule_rec.asset_tax_code2 ,
p_rule_rec.liability_tax_code2 ,
p_rule_rec.act_tax_non_rec_ccid2 ,
p_rule_rec.act_vat_tax_id2
from ar_adjustments adj ,
ar_receivables_trx rt ,
ar_rec_trx_le_details rtd ,
ra_customer_trx ctinv ,
ra_cust_trx_line_gl_dist ctlgdinv
where adj.adjustment_id = g_ae_doc_rec.source_id
and adj.status = 'A' --only approved adjustments
and adj.receivables_trx_id = rt.receivables_trx_id
and rtd.receivables_trx_id (+) = rt.receivables_trx_id
and nvl(rtd.legal_entity_id, ctinv.legal_entity_id)
= ctinv.legal_entity_id
and adj.customer_trx_id = ctinv.customer_trx_id --INV REC exchange rate Information
and adj.customer_trx_id = ctlgdinv.customer_trx_id (+) --REC account ccid
and 'REC' = ctlgdinv.account_class (+)
and 'Y' = ctlgdinv.latest_rec_flag (+) ;
select adj.adjustment_id ,
adj.customer_trx_id ,
adj.customer_trx_line_id ,
adj.payment_schedule_id ,
adj.receivables_trx_id ,
adj.code_combination_id ,
adj.apply_date ,
adj.gl_date ,
adj.type ,
adj.status ,
adj.amount ,
adj.acctd_amount ,
adj.line_adjusted ,
adj.freight_adjusted ,
adj.tax_adjusted ,
adj.receivables_charges_adjusted ,
ctinv.invoice_currency_code ,
ctinv.exchange_rate exchange_rate,
ctinv.exchange_rate_type exchange_rate_type,
ctinv.exchange_date exchange_date,
ctinv.bill_to_customer_id ,
ctinv.bill_to_site_use_id ,
ctinv.drawee_id ,
ctinv.drawee_site_use_id ,
ctinv.upgrade_method ,
ctlgdinv.code_combination_id ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL', 'ACTIVITY_GL_ACCOUNT', --trx id -12
'CHARGEBACK', 'ACTIVITY_GL_ACCOUNT', --trx id -11
'COMMITMENT', 'ACTIVITY_GL_ACCOUNT', --trx od -1
nvl(rt.gl_account_source, 'NO_SOURCE')) ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL', 'NONE', --trx id -12
'CHARGEBACK', 'NONE', --trx id -11
'COMMITMENT', 'NONE', --trx id -1
nvl(rt.tax_code_source , 'NO_SOURCE')) ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL', '',
'CHARGEBACK', '',
'COMMITMENT', '',
rt.tax_recoverable_flag) ,
decode(g_ae_doc_rec.other_flag,
'CBREVERSAL',g_ae_doc_rec.source_id_old, --chargeback reversal
'CHARGEBACK',g_ae_doc_rec.source_id_old, --chargeback
'COMMITMENT',g_ae_doc_rec.source_id_old, --commitments
'OVERRIDE' ,g_ae_doc_rec.source_id_old, --when user specifies account
rt.code_combination_id) , --in adjustment form
rt.asset_tax_code ,
rt.liability_tax_code ,
'' ,
'' ,
'NO_SOURCE' ,
'NO_SOURCE' ,
'' ,
'' ,
'' ,
'' ,
'' ,
''
into p_adj_rec.adjustment_id ,
p_adj_rec.customer_trx_id ,
p_adj_rec.customer_trx_line_id ,
p_adj_rec.payment_schedule_id ,
p_adj_rec.receivables_trx_id ,
p_adj_rec.code_combination_id ,
p_adj_rec.apply_date ,
p_adj_rec.gl_date ,
p_adj_rec.type ,
p_adj_rec.status ,
p_adj_rec.amount ,
p_adj_rec.acctd_amount ,
p_adj_rec.line_adjusted ,
p_adj_rec.freight_adjusted ,
p_adj_rec.tax_adjusted ,
p_adj_rec.receivables_charges_adjusted ,
p_cust_inv_rec.invoice_currency_code ,
p_cust_inv_rec.exchange_rate ,
p_cust_inv_rec.exchange_rate_type ,
p_cust_inv_rec.exchange_date ,
p_cust_inv_rec.bill_to_customer_id ,
p_cust_inv_rec.bill_to_site_use_id ,
p_cust_inv_rec.drawee_id ,
p_cust_inv_rec.drawee_site_use_id ,
p_cust_inv_rec.upgrade_method ,
p_ctlgd_inv_rec.code_combination_id ,
p_rule_rec.gl_account_source1 ,
p_rule_rec.tax_code_source1 ,
p_rule_rec.tax_recoverable_flag1 ,
p_rule_rec.code_combination_id1 ,
p_rule_rec.asset_tax_code1 ,
p_rule_rec.liability_tax_code1 ,
p_rule_rec.act_tax_non_rec_ccid1 ,
p_rule_rec.act_vat_tax_id1 ,
p_rule_rec.gl_account_source2 ,
p_rule_rec.tax_code_source2 ,
p_rule_rec.tax_recoverable_flag2 ,
p_rule_rec.code_combination_id2 ,
p_rule_rec.asset_tax_code2 ,
p_rule_rec.liability_tax_code2 ,
p_rule_rec.act_tax_non_rec_ccid2 ,
p_rule_rec.act_vat_tax_id2
from ar_adjustments adj ,
ar_receivables_trx rt ,
ra_customer_trx ctinv ,
ra_cust_trx_line_gl_dist ctlgdinv
where adj.adjustment_id = g_ae_doc_rec.source_id
and adj.status = 'A' --only approved adjustments
and adj.receivables_trx_id = rt.receivables_trx_id
and adj.customer_trx_id = ctinv.customer_trx_id --INV REC exchange rate Information
and adj.customer_trx_id = ctlgdinv.customer_trx_id (+) --REC account ccid
and 'REC' = ctlgdinv.account_class (+)
and 'Y' = ctlgdinv.latest_rec_flag (+) ;
| Bills receivable, or Bills Receivable account is selected|
+----------------------------------------------------------*/
IF (p_cust_inv_rec.drawee_site_use_id IS NOT null) THEN
-- MRC Trigger Replacment. Enumerated columns. Branched based
-- on primary or Reporting.
IF ( g_ae_sys_rec.sob_type = 'P') THEN
select ard.line_id,
ard.source_id,
ard.source_table,
ard.source_type,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.creation_date,
ard.created_by,
ard.last_updated_by,
ard.last_update_date,
ard.last_update_login,
ard.org_id,
ard.source_table_secondary,
ard.source_id_secondary,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.taxable_entered_dr,
ard.taxable_entered_cr,
ard.taxable_accounted_dr,
ard.taxable_accounted_cr,
ard.tax_link_id,
ard.third_party_id,
ard.third_party_sub_id,
ard.reversed_source_id,
ard.tax_code_id,
ard.location_segment_id,
ard.source_type_secondary,
ard.tax_group_code_id,
--{BUG#2979254
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id
--}
INTO p_ard_rec.line_id,
p_ard_rec.source_id,
p_ard_rec.source_table,
p_ard_rec.source_type,
p_ard_rec.code_combination_id,
p_ard_rec.amount_dr,
p_ard_rec.amount_cr,
p_ard_rec.acctd_amount_dr,
p_ard_rec.acctd_amount_cr,
p_ard_rec.creation_date,
p_ard_rec.created_by,
p_ard_rec.last_updated_by,
p_ard_rec.last_update_date,
p_ard_rec.last_update_login,
p_ard_rec.org_id,
p_ard_rec.source_table_secondary,
p_ard_rec.source_id_secondary,
p_ard_rec.currency_code,
p_ard_rec.currency_conversion_rate,
p_ard_rec.currency_conversion_type,
p_ard_rec.currency_conversion_date,
p_ard_rec.taxable_entered_dr,
p_ard_rec.taxable_entered_cr,
p_ard_rec.taxable_accounted_dr,
p_ard_rec.taxable_accounted_cr,
p_ard_rec.tax_link_id,
p_ard_rec.third_party_id,
p_ard_rec.third_party_sub_id,
p_ard_rec.reversed_source_id,
p_ard_rec.tax_code_id,
p_ard_rec.location_segment_id,
p_ard_rec.source_type_secondary,
p_ard_rec.tax_group_code_id,
--{BUG#2979254
p_ard_rec.ref_customer_trx_line_id,
p_ard_rec.ref_cust_trx_line_gl_dist_id,
p_ard_rec.ref_line_id
--}
from ar_transaction_history th,
ar_payment_schedules pay,
ar_distributions ard
where
th.transaction_history_id =
(select max(th1.transaction_history_id)
from ar_transaction_history th1
where nvl(th1.POSTABLE_FLAG, 'N') = 'Y'
and th1.status IN ('UNPAID', 'PENDING_REMITTANCE')
and th1.customer_trx_id = p_adj_rec.customer_trx_id)
and th.customer_trx_id = pay.customer_trx_id
and ard.source_id = th.transaction_history_id
and ard.source_table = 'TH'
AND ard.source_id_secondary is null
AND ard.source_table_secondary is null
AND ard.source_type_secondary is null
and (((sign(pay.amount_due_original) > 0)
and ((nvl(ard.AMOUNT_DR,0) <> 0) OR
(nvl(ard.ACCTD_AMOUNT_DR,0) <> 0))
and (nvl(ard.AMOUNT_CR,0) = 0) and
(nvl(ard.ACCTD_AMOUNT_CR,0) = 0))
OR ((sign(pay.amount_due_original) < 0)
and ((nvl(ard.AMOUNT_CR,0) <> 0) OR
(nvl(ard.ACCTD_AMOUNT_CR,0) <> 0))
and (nvl(ard.AMOUNT_DR,0) = 0) and
(nvl(ard.ACCTD_AMOUNT_DR,0) = 0)));
SELECT tax_rate_id
INTO l_tax_rate_id
FROM zx_sco_rates
WHERE tax_rate_code = p_rule_rec.asset_tax_code1
AND p_adj_rec.apply_date BETWEEN
NVL(effective_from, p_adj_rec.apply_date) AND
NVL(effective_to, p_adj_rec.apply_date);
/**Above proc call will update the invoice header record to database.As l_cust_inv_rec
is fetched from db prior to the update,manually setting it with new value */
IF nvl(l_tmp_upg_method,'R12_NLB') = 'R12_MERGE' THEN
l_cust_inv_rec.upgrade_method := l_tmp_upg_method;
| Endorsments, we need to update the link id so the last Transaction |
| History Record must be for Matured Pending Risk elimination Endorsment|
+-----------------------------------------------------------------------*/
update ar_adjustments
set link_to_trx_hist_id = (select max(th.transaction_history_id)
from ar_transaction_history th
where th.customer_trx_id = p_adj_rec.customer_trx_id
and th.event = 'MATURITY_DATE'
and exists (select 'x'
from ar_distributions ard
where ard.source_id = th.transaction_history_id
and ard.source_table = 'TH'))
where adjustment_id = p_adj_rec.adjustment_id;
select min(code_combination_id)
into l_code_combination_id
from ra_cust_trx_line_gl_dist ctlgd
where ctlgd.account_class = 'TAX'
and ctlgd.account_set_flag = 'N'
and ctlgd.customer_trx_id = p_adj_rec.customer_trx_id;
select
min(decode(alv.location_segment_id,
'',avt.adj_non_rec_tax_ccid,
alv.adj_non_rec_tax_ccid))
into l_code_combination_id
FROM ra_customer_trx_lines ctl,
ar_vat_tax avt,
ar_location_accounts alv
where ctl.customer_trx_id = p_adj_rec.customer_trx_id
and ctl.line_type = 'TAX'
and ctl.location_segment_id = alv.location_segment_id(+)
and ctl.vat_tax_id = avt.vat_tax_id(+);