[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_TH(p_ae_deleted OUT NOCOPY BOOLEAN);
| PUBLIC PROCEDURE Delete_Acct
|
| DESCRIPTION
| Accounting Entry Deletion
| -------------------------
| This procedure is the Accounting Entry deletion routine which
| deletes the accounting associated with Transaction history 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 OUT NOCOPY 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_BILLS_RECEIVABLE_MAIN.Delete_Acct()+');
Delete_TH(p_ae_deleted => p_ae_deleted) ;
arp_standard.debug( 'ARP_BILLS_RECEIVABLE_MAIN.Delete_Acct()-');
arp_standard.debug( 'EXCEPTION: ARP_BILLS_RECEIVABLE_MAIN.Delete_Acct');
END Delete_Acct;
| PROCEDURE Delete_TH
|
| DESCRIPTION
| Deletes accounting associated with a Transaction History 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.
| NOTE
| If this routine is called there must be accounting for the
| parent Transaction history id in the distributions table
| otherwise a NO_DATA_FOUND exception will be raised. In other
| words the routine call should be valid.
|
| PARAMETERS
| p_ae_deleted indicates whether records were deleted
| for source_id
* ======================================================================*/
PROCEDURE Delete_TH(p_ae_deleted OUT NOCOPY BOOLEAN) IS
l_trans_hist ar_transaction_history.transaction_history_id%TYPE;
arp_standard.debug('ARP_BILLS_RECEIVABLE_MAIN.Delete_TH()+');
SELECT th.transaction_history_id
INTO l_trans_hist
FROM ar_transaction_history th
WHERE th.transaction_history_id = g_ae_doc_rec.source_id
AND th.gl_posted_date is null
AND th.posting_control_id = -3
AND th.postable_flag = 'Y'
AND g_ae_doc_rec.source_table = C_TH;
| Delete all accounting for source id and source table combination |
| if valid candidate for deletion, if not then a NO_DATA_FOUND |
| exception will be raised by the above select statement. |
+-------------------------------------------------------------------*/
DELETE FROM AR_DISTRIBUTIONS
WHERE source_id = g_ae_doc_rec.source_id
AND source_table = C_TH
RETURNING line_id
BULK COLLECT INTO l_ar_dist_key_value_list;
arp_standard.debug('ARP_BILLS_RECEIVABLE_MAIN.Delete_TH()-');
arp_standard.debug('ARP_BILLS_RECEIVABLE_MAIN.Delete_TH - NO_DATA_FOUND' );
p_ae_deleted := FALSE;
arp_standard.debug('EXCEPTION: ARP_BILLS_RECEIVABLE_MAIN.Delete_TH');
p_ae_deleted := FALSE;
END Delete_TH;
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,
ard.from_amount_dr,
ard.from_amount_cr,
ard.from_acctd_amount_dr,
ard.from_acctd_amount_cr
--}
from ar_distributions ard
where NVL(g_ae_sys_rec.sob_type,'P') = 'P'
and ard.source_id = g_ae_doc_rec.source_id_old
and ard.source_table = g_ae_doc_rec.source_table
order by line_id ;
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,
ard.from_amount_dr,
ard.from_amount_cr,
ard.from_acctd_amount_dr,
ard.from_acctd_amount_cr
--}
from ar_mc_distributions_all ard
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
order by line_id ;
l_ard_rec.last_updated_by,
l_ard_rec.last_update_date,
l_ard_rec.last_update_login,
l_ard_rec.org_id,
l_ard_rec.source_table_secondary,
l_ard_rec.source_id_secondary,
l_ard_rec.currency_code,
l_ard_rec.currency_conversion_rate,
l_ard_rec.currency_conversion_type,
l_ard_rec.currency_conversion_date,
l_ard_rec.taxable_entered_dr,
l_ard_rec.taxable_entered_cr,
l_ard_rec.taxable_accounted_dr,
l_ard_rec.taxable_accounted_cr,
l_ard_rec.tax_link_id,
l_ard_rec.third_party_id,
l_ard_rec.third_party_sub_id,
l_ard_rec.reversed_source_id,
l_ard_rec.tax_code_id,
l_ard_rec.location_segment_id,
l_ard_rec.source_type_secondary,
l_ard_rec.tax_group_code_id,
--{BUG#2979254
l_ard_rec.ref_customer_trx_line_id,
l_ard_rec.ref_cust_trx_line_gl_dist_id,
l_ard_rec.ref_line_id,
l_ard_rec.from_amount_dr,
l_ard_rec.from_amount_cr,
l_ard_rec.from_acctd_amount_dr,
l_ard_rec.from_acctd_amount_cr;
select '',
pay.customer_trx_id,
pay.payment_schedule_id,
pay.amount_due_remaining,
--{BUG#4301323
-- DECODE(g_ae_sys_rec.sob_type, 'P',
-- pay.acctd_amount_due_remaining,
-- arp_mrc_acct_main.get_ps_entity_data(
-- pay.payment_schedule_id,
-- g_ae_sys_rec.set_of_books_id)) acctd_amount_due_remaining,
pay.acctd_amount_due_remaining acctd_amount_due_remaining,
--}
pay.amount_line_items_remaining,
pay.tax_remaining,
pay.freight_remaining,
pay.receivables_charges_remaining,
'Y',
ctinv.invoice_currency_code,
--{BUG#4301323
-- 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,
--}
--{BUG#4301323
-- 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,
--}
--{BUG#4301323
-- 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,
ctinv.bill_to_customer_id,
ctinv.bill_to_site_use_id,
ctinv.drawee_site_use_id,
th.customer_trx_id,
th.status,
th.event,
th.prv_trx_history_id
into p_app_rec.receivable_application_id,
p_app_rec.applied_customer_trx_id,
p_app_rec.applied_payment_schedule_id,
p_app_rec.amount_applied,
p_app_rec.acctd_amount_applied_to,
p_app_rec.line_applied,
p_app_rec.tax_applied,
p_app_rec.freight_applied,
p_app_rec.receivables_charges_applied,
p_app_rec.confirmed_flag,
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.trx_date,
p_cust_inv_rec.bill_to_customer_id, --3rd party
p_cust_inv_rec.bill_to_site_use_id, --3rd party sub id
p_cust_inv_rec.drawee_site_use_id,
p_trh_rec.customer_trx_id,
p_trh_rec.status,
p_trh_rec.event,
p_trh_rec.prv_trx_history_id
from ar_transaction_history th,
ra_customer_trx ctinv,
ar_payment_schedules pay
where th.transaction_history_id = g_ae_doc_rec.source_id
and th.customer_trx_id = ctinv.customer_trx_id
and ctinv.customer_trx_id = pay.customer_trx_id;
select 'NO_SOURCE', --gl account source
'NO_SOURCE', -- tax code source
'', -- tax recoverable flag
'', -- discount ccid
'', --asset tax code
'', --liability tax code
'' ,
'' ,
'NO_SOURCE',
'NO_SOURCE',
'',
'',
'',
'',
'' ,
''
into p_rule_rec.gl_account_source1, --Initialize Earned discounts
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, --Initialize Unearned discounts
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 dual;
l_ae_deleted BOOLEAN := FALSE;
SELECT status
FROM ar_transaction_history
WHERE transaction_history_id = p_transaction_history_id;
select ps.amount_due_original,
ps.amount_due_remaining,
--{BUG4301323
-- DECODE(g_ae_sys_rec.sob_type, 'P',
-- ps.acctd_amount_due_remaining,
-- arp_mrc_acct_main.get_ps_entity_data(
-- ps.payment_schedule_id,
-- g_ae_sys_rec.set_of_books_id)) acctd_amount_due_remaining
ps.acctd_amount_due_remaining acctd_amount_due_remaining
--}
from ar_payment_schedules ps
where ps.payment_schedule_id = p_ps_id;
SELECT *
FROM ar_transaction_history
WHERE postable_flag = 'Y'
AND status = NVL(p_status,status)
AND event = NVL(p_event,event)
CONNECT BY PRIOR prv_trx_history_id = transaction_history_id
START WITH transaction_history_id = p_transaction_history_id
ORDER BY transaction_history_id desc;
| whether to select debut or credit amount
| p_dist_rec Distribution record
* ======================================================================*/
PROCEDURE find_rec_dist_record(p_transaction_history_id IN ar_transaction_history.transaction_history_id%TYPE,
p_sign IN NUMBER,
p_dist_rec OUT NOCOPY ar_distributions%ROWTYPE) IS
/*-----------------------------------------------------+
| Cursor to return the accounting record for |
| given transaction history record. This is used to |
| in reclassification reversing the |
| receivable accounting. |
+-----------------------------------------------------*/
-- MRC Trigger Replacement: Enumerated cursor and added union for
-- Reporting SOB data
CURSOR distribution_cur IS
select line_id,
source_id,
source_table,
source_type,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
org_id,
source_table_secondary,
source_id_secondary,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
third_party_id,
third_party_sub_id,
reversed_source_id,
tax_code_id,
location_segment_id,
source_type_secondary,
tax_group_code_id,
--{BUG#2979254
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr
--}
from ar_distributions
where NVL(g_ae_sys_rec.sob_type,'P') = 'P'
and source_id = p_transaction_history_id
and source_table = C_TH
and source_type in (C_REC,C_FACTOR,C_REMITTANCE,C_UNPAIDREC)
AND source_id_secondary is null
AND source_table_secondary is null
AND source_type_secondary is null
and (((sign(p_sign) > 0)
and ((nvl(AMOUNT_DR,0) <> 0) OR (nvl(ACCTD_AMOUNT_DR,0) <> 0))
and (nvl(AMOUNT_CR,0) = 0) and (nvl(ACCTD_AMOUNT_CR,0) = 0))
OR ((sign(p_sign) < 0)
and ((nvl(AMOUNT_CR,0) <> 0) OR (nvl(ACCTD_AMOUNT_CR,0) <> 0))
and (nvl(AMOUNT_DR,0) = 0) and (nvl(ACCTD_AMOUNT_DR,0) = 0)))
order by line_id desc;
select line_id,
source_id,
source_table,
source_type,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
org_id,
source_table_secondary,
source_id_secondary,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
third_party_id,
third_party_sub_id,
reversed_source_id,
tax_code_id,
location_segment_id,
source_type_secondary,
tax_group_code_id,
--{BUG#2979254
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr
--}
from ar_mc_distributions_all
where g_ae_sys_rec.sob_type = 'R'
and set_of_books_id = g_ae_sys_rec.set_of_books_id
and source_id = p_transaction_history_id
and source_table = C_TH
and source_type in (C_REC,C_FACTOR,C_REMITTANCE,C_UNPAIDREC)
AND source_id_secondary is null
AND source_table_secondary is null
AND source_type_secondary is null
and (((sign(p_sign) > 0)
and ((nvl(AMOUNT_DR,0) <> 0) OR (nvl(ACCTD_AMOUNT_DR,0) <> 0))
and (nvl(AMOUNT_CR,0) = 0) and (nvl(ACCTD_AMOUNT_CR,0) = 0))
OR ((sign(p_sign) < 0)
and ((nvl(AMOUNT_CR,0) <> 0) OR (nvl(ACCTD_AMOUNT_CR,0) <> 0))
and (nvl(AMOUNT_DR,0) = 0) and (nvl(ACCTD_AMOUNT_DR,0) = 0)))
order by line_id desc;
distribution_rec.last_updated_by,
distribution_rec.last_update_date,
distribution_rec.last_update_login,
distribution_rec.org_id,
distribution_rec.source_table_secondary,
distribution_rec.source_id_secondary,
distribution_rec.currency_code,
distribution_rec.currency_conversion_rate,
distribution_rec.currency_conversion_type,
distribution_rec.currency_conversion_date,
distribution_rec.taxable_entered_dr,
distribution_rec.taxable_entered_cr,
distribution_rec.taxable_accounted_dr,
distribution_rec.taxable_accounted_cr,
distribution_rec.tax_link_id,
distribution_rec.third_party_id,
distribution_rec.third_party_sub_id,
distribution_rec.reversed_source_id,
distribution_rec.tax_code_id,
distribution_rec.location_segment_id,
distribution_rec.source_type_secondary,
distribution_rec.tax_group_code_id,
--{BUG#2979254
distribution_rec.ref_customer_trx_line_id,
distribution_rec.ref_cust_trx_line_gl_dist_id,
distribution_rec.ref_line_id,
distribution_rec.from_amount_dr,
distribution_rec.from_amount_cr,
distribution_rec.from_acctd_amount_dr,
distribution_rec.from_acctd_amount_cr;
select line_id,
source_id,
source_table,
source_type,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
org_id,
source_table_secondary,
source_id_secondary,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
third_party_id,
third_party_sub_id,
reversed_source_id,
tax_code_id,
location_segment_id,
source_type_secondary,
tax_group_code_id,
--{BUG#2979254
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr
--}
FROM ar_distributions
WHERE NVL(g_ae_sys_rec.sob_type,'P') = 'P'
AND source_id = l_transaction_history_id
AND source_table = C_TH
AND source_type = C_REC
AND source_id_secondary is not null
AND source_table_secondary = C_CTL
AND source_type_secondary = C_ASSIGNMENT
ORDER BY line_id ASC;
select line_id,
source_id,
source_table,
source_type,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
org_id,
source_table_secondary,
source_id_secondary,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
third_party_id,
third_party_sub_id,
reversed_source_id,
tax_code_id,
location_segment_id,
source_type_secondary,
tax_group_code_id,
--{BUG#2979254
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr
--}
FROM ar_mc_distributions_all
WHERE g_ae_sys_rec.sob_type = 'R'
AND set_of_books_id = g_ae_sys_rec.set_of_books_id
AND source_id = l_transaction_history_id
AND source_table = C_TH
AND source_type = C_REC
AND source_id_secondary is not null
AND source_table_secondary = C_CTL
AND source_type_secondary = C_ASSIGNMENT
ORDER BY line_id ASC;
last_exchange_accounting_rec.last_updated_by,
last_exchange_accounting_rec.last_update_date,
last_exchange_accounting_rec.last_update_login,
last_exchange_accounting_rec.org_id,
last_exchange_accounting_rec.source_table_secondary,
last_exchange_accounting_rec.source_id_secondary,
last_exchange_accounting_rec.currency_code,
last_exchange_accounting_rec.currency_conversion_rate,
last_exchange_accounting_rec.currency_conversion_type,
last_exchange_accounting_rec.currency_conversion_date,
last_exchange_accounting_rec.taxable_entered_dr,
last_exchange_accounting_rec.taxable_entered_cr,
last_exchange_accounting_rec.taxable_accounted_dr,
last_exchange_accounting_rec.taxable_accounted_cr,
last_exchange_accounting_rec.tax_link_id,
last_exchange_accounting_rec.third_party_id,
last_exchange_accounting_rec.third_party_sub_id,
last_exchange_accounting_rec.reversed_source_id,
last_exchange_accounting_rec.tax_code_id,
last_exchange_accounting_rec.location_segment_id,
last_exchange_accounting_rec.source_type_secondary,
last_exchange_accounting_rec.tax_group_code_id,
--{BUG#2979254
last_exchange_accounting_rec.ref_customer_trx_line_id,
last_exchange_accounting_rec.ref_cust_trx_line_gl_dist_id,
last_exchange_accounting_rec.ref_line_id,
last_exchange_accounting_rec.from_amount_dr,
last_exchange_accounting_rec.from_amount_cr,
last_exchange_accounting_rec.from_acctd_amount_dr,
last_exchange_accounting_rec.from_acctd_amount_cr;
SELECT ctl.br_ref_customer_trx_id source_trx_id,
cnt.br_ref_customer_trx_id cnt_br_ref_trx_id,
ctl.br_ref_payment_schedule_id source_ps_id,
ctl.customer_trx_line_id,
adj.amount adjustment_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)) adjustment_acctd_amount,
adj.acctd_amount adjustment_acctd_amount,
--}
--{HYUDETDIST
adj.adjustment_id adjustment_id,
adj.line_adjusted adjustment_line_amount,
adj.freight_adjusted adjustment_freight_amount,
adj.tax_adjusted adjustment_tax_amount,
adj.receivables_charges_adjusted adjustment_charges_amount,
adj.payment_schedule_id adjustment_ps_id,
adj.type adjustment_type,
--}
--{BUG#5016132
adj.receivables_trx_id adj_rec_trx_id,
adj.adjustment_type adj_adj_type,
--}
assignment_ps.class,
assignment_ps.customer_id assignment_customer_id,
assignment_ps.customer_site_use_id assignment_site_use_id,
assignment_ps.amount_due_original assignment_amount_due_original,
ps.invoice_currency_code,
--{BUG4301323
-- decode(g_ae_sys_rec.sob_type, 'P',
-- ps.exchange_rate,
-- arp_mrc_acct_main.get_ps_exg_rate(
-- ps.payment_schedule_id,
-- g_ae_sys_rec.set_of_books_id)) exchange_rate,
ps.exchange_rate exchange_rate,
--}
--{BUG4301323
-- decode(g_ae_sys_rec.sob_type, 'P',
-- ps.exchange_rate_type,
-- arp_mrc_acct_main.get_ps_exg_rate_type(
-- ps.payment_schedule_id,
-- g_ae_sys_rec.set_of_books_id)) exchange_rate_type,
ps.exchange_rate_type exchange_rate_type,
--}
--{BUG4301323
-- decode(g_ae_sys_rec.sob_type, 'P',
-- ps.exchange_date,
-- arp_mrc_acct_main.get_ps_exg_date(
-- ps.payment_schedule_id,
-- g_ae_sys_rec.set_of_books_id)) exchange_date
ps.exchange_date exchange_date
--}
FROM ra_customer_trx_lines ctl,
ar_payment_schedules ps,
ar_payment_schedules assignment_ps,
ar_adjustments adj,
( SELECT distinct ctl_in.customer_trx_line_id customer_trx_line_id,
connect_by_root(br_ref_customer_trx_id) br_ref_customer_trx_id
FROM ra_customer_trx_lines ctl_in
WHERE ctl_in.br_ref_customer_trx_id is not null
START WITH ctl_in.customer_trx_id = l_customer_trx_id
CONNECT BY PRIOR ctl_in.br_ref_customer_trx_id = ctl_in.customer_trx_id) cnt
WHERE assignment_ps.payment_schedule_id = ctl.br_ref_payment_schedule_id
AND ps.customer_trx_id = ctl.customer_trx_id
AND adj.adjustment_id = ctl.br_adjustment_id
/* Bug 8558443 */
AND assignment_ps.class <> 'BR'
AND ctl.customer_trx_line_id = cnt.customer_trx_line_id;
SELECT * FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT dist.code_combination_id
FROM ra_cust_trx_line_gl_dist dist
WHERE dist.customer_trx_id = l_customer_trx_id
AND dist.account_class = C_REC
AND dist.latest_rec_flag = 'Y';
SELECT th.transaction_history_id
FROM ar_transaction_history th
WHERE th.customer_trx_id = l_customer_trx_id
AND th.current_accounted_flag = 'Y';
select line_id,
source_id,
source_table,
source_type,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
org_id,
source_table_secondary,
source_id_secondary,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
third_party_id,
third_party_sub_id,
reversed_source_id,
tax_code_id,
location_segment_id,
source_type_secondary,
tax_group_code_id,
--{BUG#2979254
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr
--}
FROM ar_distributions
WHERE NVL(g_ae_sys_rec.sob_type,'P') = 'P'
AND source_id = l_transaction_history_id
AND source_table = C_TH
AND source_type in (C_DEFERRED_TAX,C_TAX)
ORDER BY line_id ASC;
select line_id,
source_id,
source_table,
source_type,
code_combination_id,
amount_dr,
amount_cr,
acctd_amount_dr,
acctd_amount_cr,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
org_id,
source_table_secondary,
source_id_secondary,
currency_code,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
taxable_entered_dr,
taxable_entered_cr,
taxable_accounted_dr,
taxable_accounted_cr,
tax_link_id,
third_party_id,
third_party_sub_id,
reversed_source_id,
tax_code_id,
location_segment_id,
source_type_secondary,
tax_group_code_id,
--{BUG#2979254
ref_customer_trx_line_id,
ref_cust_trx_line_gl_dist_id,
ref_line_id,
from_amount_dr,
from_amount_cr,
from_acctd_amount_dr,
from_acctd_amount_cr
--}
FROM ar_mc_distributions_all
WHERE g_ae_sys_rec.sob_type = 'R'
AND set_of_books_id = g_ae_sys_rec.set_of_books_id
AND source_id = l_transaction_history_id
AND source_table = C_TH
AND source_type in (C_DEFERRED_TAX,C_TAX)
ORDER BY line_id ASC;
tax_accounting_rec.last_updated_by,
tax_accounting_rec.last_update_date,
tax_accounting_rec.last_update_login,
tax_accounting_rec.org_id,
tax_accounting_rec.source_table_secondary,
tax_accounting_rec.source_id_secondary,
tax_accounting_rec.currency_code,
tax_accounting_rec.currency_conversion_rate,
tax_accounting_rec.currency_conversion_type,
tax_accounting_rec.currency_conversion_date,
tax_accounting_rec.taxable_entered_dr,
tax_accounting_rec.taxable_entered_cr,
tax_accounting_rec.taxable_accounted_dr,
tax_accounting_rec.taxable_accounted_cr,
tax_accounting_rec.tax_link_id,
tax_accounting_rec.third_party_id,
tax_accounting_rec.third_party_sub_id,
tax_accounting_rec.reversed_source_id,
tax_accounting_rec.tax_code_id,
tax_accounting_rec.location_segment_id,
tax_accounting_rec.source_type_secondary,
tax_accounting_rec.tax_group_code_id,
--{BUG#2979254
tax_accounting_rec.ref_customer_trx_line_id,
tax_accounting_rec.ref_cust_trx_line_gl_dist_id,
tax_accounting_rec.ref_line_id,
tax_accounting_rec.from_amount_dr,
tax_accounting_rec.from_amount_cr,
tax_accounting_rec.from_acctd_amount_dr,
tax_accounting_rec.from_acctd_amount_cr;